DQL操作

权限说明

Dataphin中使用DQL语句需要具备查询表数据的权限。配置说明，请参见申请、续期和交还表权限。

目录

• Select

• Join

• Join-Cross Join

• Join-内连接 Inner Join

• Join-外连接 Outer Join

• Join-自连接

• Join - 左半连接

• Join-不等值连接

• Map Join

• 去重Union

• 不去重Uion ALL

• 去重 INTERSECT

• 不去重 INTERSECT ALL

• 去重 EXCEPT

• 不去重 EXCEPT

  说明

  若您需对Dataphin中逻辑表进行DQL操作，请参见逻辑表DQL操作。

Select

select column[s]
from <table table_name>|<view view_name]
[join table
on 条件表达式]
[where 条件表达式]
[group by column[s]]
[having 条件表达式]
[order by column[s] asc|desc]
[limit m [,n]];

更多信息请参见Select。

Join

SELECT select_expression, select_expression, ... FROM table_reference JOIN table_reference JOIN table_reference, ... [ON join_condition];

更多信息请参见Join。

Join-Cross Join

SELECT table1.col1, table2.col2, ... FROM table1 CROSS JOIN table2;

更多信息请参见Join-Cross Join。

Join-内连接 Inner Join

SELECT select_expression, select_expression, ... FROM table_refernce1 (JOIN|INNER JOIN) table_reference2 ON (table_reference1.column_1 = table_reference2.column_a);

SELECT table1.col1, table2.col2, ... FROM table1, table2 WHERE table1.col3 = table2.col1; 

SELECT table1.col1, table1.col2, ... table2.col1, table2.col3, ... FROM table1 NATURAL JOIN table2; 

更多信息请参见Join-内连接 Inner Join。

Join-外连接 Outer Join

SELECT select_expression, select_expression, ... FROM table_reference (LEFT|RIGHT|FULL) OUTER JOIN table reference;

更多信息请参见Join-外连接 Outer Join。

Join-自连接

SELECT select_expression, select_expression, ... FROM table_reference alias1 JOIN table_reference alias2 ON (join_condition);

更多信息请参见Join-自连接。

Join - 左半连接

SELECT select_expression, select_expression, ... FROM table_reference_1 LEFT SEMI JOIN table_reference_2 ON (join_condition);

更多信息请参见Join - 左半连接。

Join-不等值连接

SELECT select_expression, select_expression, ... FROM table_reference1 JOIN table reference_2 ON equi_join_condition WHERE non_equi_join_condition

更多信息请参见Join-不等值连接。

Map Join

SELECT /*+ MAPJOIN(table_b) */ select_expression, select_expression, ...FROM table_a JOIN table_b ON join_condition

更多信息请参见Map Join。

去重Union

SELECT * FROM (
select_statement
UNION
select_statement
);

更多信息请参见去重Union。

不去重Uion ALL

SELECT * FROM (
select_statement
UNION ALL
select_statement
);

更多信息请参见不去重Uion ALL。

去重 INTERSECT

SELECT * FROM (
select_statement
INTERSECT
select_statement
);

更多信息请参见去重 INTERSECT。

不去重 INTERSECT ALL

SELECT * FROM (
select_statement
INTERSECT ALL
select_statement
);

更多信息请参见不去重 INTERSECT ALL。

去重 EXCEPT

SELECT * FROM (
select_statement
EXCEPT
select_statement
);

更多信息请参见去重 EXCEPT。

不去重 EXCEPT

SELECT * FROM (
select_statement
EXCEPT ALL
select_statement
);

更多信息请参见不去重 EXCEPT。