DQL operations

Feature

Supported

Command format

Yes

List expression (select_expr)

• Supports *

• Supports distinct

• Does not support regular expressions

WHERE clause (where_condition)

Dataphin 2.9.2 and later versions support partition pruning for user-defined functions (UDFs).

When the GROUP BY property is set, i.e., set odps.sql.groupby.position.alias=true;, integer constants in the GROUP BY clause are treated as the ordinal numbers of columns in the SELECT list. For more information, see GROUP BY query (col_list)

Yes

Basic subquery

• Supports select {select_expr} from ({select_statement}) {sq_alias_name};

• Does not support select ({select_statement}) from {table_name};

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

• Supports union all

• Supports union [distinct]

Complement

• Supports except

• Supports except all

• Supports minus

• Does not support except distinct

• Does not support minus distinct

• Does not support minus distinct

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