DQL operations

Permissions

DQL statements require the permission to query table data. For more information, see Apply for, Renew, and Return Table Permissions.

Contents

• Select

• Join

• Cross join

• Inner join

• Outer join

• Self join

• Left semi join

• Non-equi join

• Map join

• Union

• Union all

• Intersect

• Intersect all

• Except

• Except all

  Note

  To perform DQL operations on logical tables in Dataphin, see DQL Operations for Logical Tables.

Select

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

For more information, see Select.

Join

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

For more information, see Join.

Cross join

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

For more information, see Cross join.

Inner join

SELECT select_expression, select_expression, ... FROM table_reference1 (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; 

For more information, see Inner join.

Outer join

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

For more information, see Outer join.

Self join

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

For more information, see Self join.

Left semi join

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

For more information, see Left semi join.

Non-equi join

SELECT select_expression, select_expression, ... FROM table_reference1 JOIN table_reference_2 ON equi_join_condition WHERE non_equi_join_condition

For more information, see Non-equi join.

Map join

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

For more information, see Map join.

Union

SELECT * FROM (
select_statement
UNION
select_statement
);

For more information, see Union.

Union all

SELECT * FROM (
select_statement
UNION ALL
select_statement
);

For more information, see Union all.

Intersect

SELECT * FROM (
select_statement
INTERSECT
select_statement
);

For more information, see Intersect.

Intersect all

SELECT * FROM (
select_statement
INTERSECT ALL
select_statement
);

For more information, see Intersect all.

Except

SELECT * FROM (
select_statement
EXCEPT
select_statement
);

For more information, see Except.

Except all

SELECT * FROM (
select_statement
EXCEPT ALL
select_statement
);

For more information, see Except all.