This topic lists SQL statements that are supported by MaxCompute by comparing MaxCompute SQL statements with SQL statements of Hive, MySQL, Oracle, and SQL Server.
Support for DDL statements
| Statement | MaxCompute | Hive | MySQL | Oracle | SQL Server |
|---|---|---|---|---|---|
| CREATE TABLE-PRIMARY KEY | N | N | Y | Y | Y |
| CREATE TABLE-NOT NULL | Y | N | Y | Y | Y |
| CREATE TABLE-CLUSTER BY | Y | Y | N | Y | Y |
| CREATE TABLE-EXTERNAL TABLE | Y (OSS, OTS, TDDL) | Y | N | Y | N |
| CREATE TABLE-TEMPORARY TABLE | N | Y | Y | Y | Y (with the # prefix) |
| INDEX-CREATE INDEX | N | Y | Y | Y | Y |
| VIRTUAL COLUMN | N | N | N | Y | Y |
Support for DML statements
| Statement | MaxCompute | Hive | MySQL | Oracle | SQL Server |
|---|---|---|---|---|---|
| CTE | Y | Y | Y | Y | Y |
| SELECT-recursive CTE | N | N | N | Y | Y |
| SELECT-GROUP BY ROLL UP | Y | Y | Y | Y | Y |
| SELECT-GROUP BY CUBE | Y | Y | N | Y | Y |
| SELECT-GROUPING SET | Y | Y | N | Y | Y |
| SELECT-IMPLICIT JOIN | Y | Y | N | Y | Y |
| SELECT-PIVOT | N | N | N | Y | Y |
| SEMI JOIN | Y | Y | Y | N | N |
| SELEC TRANSFROM | Y | Y | N | N | N |
| SELECT-correlated subquery | Y | Y | Y | Y | Y |
| ORDER BY NULLS FIRST/LAST | N | Y | Y | Y | Y |
| LATERAL VIEW | Y | Y | N | Y | Y (CROSS APPLY keyword) |
| SET OPERATOR-UNION (distinct) | Y | Y | Y | Y | Y |
| SET OPERATOR-INTERSECT | Y | N | N | Y | Y |
| SET OPERATOR-MINUS/EXCEPT | Y | N | N | Y | Y (keyword EXCEPT) |
| INSERT INTO ... VALUES | Y | Y | Y | Y | Y |
| INSERT INTO (ColumnList) | Y | Y | Y | Y | Y |
| UPDATE … WHERE | N | Y | Y | Y | Y |
| UPDATE … ORDER BY LIMIT | N | N | Y | N | Y |
| DELETE … WHERE | N | Y | Y | Y | Y |
| DELETE … ORDER BY LIMIT | N | N | Y | N | N |
| ANALYTIC-reusable WINDOWING CLUSUE | Y | Y | N | N | N |
| ANALYTIC-CURRENT ROW | Y | Y | N | Y | Y |
| ANALYTIC-UNBOUNDED | Y | N | Y | Y | Y |
| ANALYTIC-RANGE ... | N | Y | N | Y | Y |
| WHILE DO | N | N | Y | Y | Y |
Support for SCRIPTING statements
| Statement | MaxCompute | Hive | MySQL | Oracle | SQL Server |
|---|---|---|---|---|---|
| TABLE VARIABLE | Y | Y | Y | Y | Y |
| SCALER VARIABLE | Y | Y | Y | Y | Y |
| ERROR HANDLING-RAISE ERROR | N | N | Y | Y | Y |
| ERROR HANDLING-TRY CATCH | N | N | N | Y | Y |
| FLOW CONTROL-LOOP | N | N | Y | Y | Y |
| CURSOR | N | N | Y | Y | Y |