Dataphin:DQL operation

Feature

Is supported

Command format

Yes

List expression (select_expr)

• Provides support for*

• Provides support for the distinct operation.

• Does not support regular expressions

WHERE clause (where_condition)

Supported by Dataphin 2.9.2 and later versions for UDF partition cropping

When the SQL statement sets the group by property, i.e., set odps.sql.groupby.position.alias=true;, the integer constants in group by will be treated as the ordinal number of the select columns. For more information, see GROUP BY group query (col_list)

Yes

Basic subquery

• Supports the use of the following syntax: SELECT <select_expr> FROM (<select_statement>) <sq_alias_name>;

• The use ofselect (<select_statement>) from <table_name>; is not supported.

IN SUBQUERY

Yes

NOT EXISTS SUBQUERY

Yes

Scalar subquery

Yes

Intersection, Union, and Complement

• Supports intersect

• Does not support intersect all

• Does not support intersect distinct

Union

• Provides support forunion all

• Includes support forunion [distinct]

Complement

• Includes support for theexcept clause.

• Includes support for theexcept all clause.

• Provides support for theminus operation.

• Does not support EXCEPT DISTINCT

• Does not support MINUS DISTINCT operations.

• Does not support MINUS DISTINCT operations.

JOIN

Yes

SEMI JOIN

Yes

MAPJOIN HINT

Yes

Lateral View

Yes

Grouping sets

Yes

Common Table Expression (CTE)

Yes

CLONE TABLE

No

SELECT TRANSFORM

No

EXPLAIN

No

Order of SELECT statement

No

GROUPING SETS

Yes

TABLESAMPLE Sampling

Yes

QUALIFY

Yes

PIVOT and UNPIVOT

Yes