All Products
Search
Document Center

SELECT

Last Updated: Jun 18, 2021

The syntax of SELECT statements is complex. This topic describes the syntax of simple SELECT statements, SELECT statements that contain set operators, and SELECT statements that contain WITH clauses.

SIMPLE SELECT

Description

You can execute a simple SELECT statement to query table data.

Syntax

simple_select:
SELECT [/*+ hint statement */] [DISTINCT | UNIQUE | ALL] 
    select_expr_list FROM from_list [WHERE condition] 
  [GROUP BY group_expression_list] [ROLLUP group_expression_list] [HAVING condition]]
  [ORDER BY order_expression_list]
  [FOR UPDATE]

select_expr:
    table_name.*
  | table_alias_name.*
  | expr [[AS] column_alias_name]
  
from_list:
    table_reference [, table_reference ...]

table_reference:
      simple_table
    | joined_table

simple_table:
    table_factor [partition_option] [[AS] table_alias_name]
    | (select_stmt) [AS] table_alias_name
    | (table_reference_list)

joined_table:
      table_reference [INNER] JOIN simple_table [join_condition]
    | table_reference outer_join_type JOIN simple_table join_condition

partition_option:
    PARTITION (partition_name_list)

partition_name_list:
    partition_name [, partition_name ...]

outer_join_type:
    {LEFT | RIGHT | FULL} [OUTER]

join_condition:
    ON expression

condition:
    expression

group_expression_list:
    group_expression [, group_expression ...]

group_expression:
    expression [ASC | DESC]

order_expression_list:
    order_expression [, order_expression ...]

order_expression:
    expression [ASC | DESC]

Parameters

Parameter

Description

DISTINCT

UNIQUE

ALL

Specifies whether to return distinct table rows. A database table may contain duplicate rows.

  • If you add DISTINCT to the statement, only distinct rows are returned.

  • If you add UNIQUE to the statement, only distinct rows are returned.

  • If you add ALL to the statement, all the matched rows are returned, including the duplicate rows. By default, ALL is used.

select_expr

The expressions or column names to query. To specify multiple expressions or column names, separate them with commas (,). You can use an asterisk (*) to query all the columns.

AS othername

Rename the output fields.

FROM table_references

The tables from which you want to query data. You can query data from multiple tables.

WHERE where_conditions

The filter conditions. The query results contain the rows that match the filter conditions. This clause is optional. where_conditions specifies an expression.

GROUP BY group_by_list

Group data based on specified fields and generate statistics.

ROLLUP group_expression_list

Merge the groups generated by the Group By statement and generate statistics.

HAVING search_confitions

The filter conditions. HAVING clauses are similar to WHERE clauses. The difference between them is that you can use aggregate functions in HAVING clauses, such as SUM and AVG.

ORDER BY order_list

order_list :

colname [ASC | DESC] [,colname [ASC | DESC]…]

Display the query results in ascending or descending order. ASC indicates the ascending order and DESC indicates the descending order. If you do not specify the order, the default order ASC is used.

FOR UPDATE

Place an exclusive lock on each row of the query results. This prevents other transactions from concurrently updating the rows. This also prevents other transactions from concurrently reading the rows for which some transaction isolation levels are specified.

PARTITION(partition_list)

The partition information of the specified tables. Format: partition(p0,p1...)

Examples

The following examples are based on table a.

image.png

  • Query the name column from table a.

SELECT name FROM a; 

image.png

  • Return the distinct rows for the name column.

SELECT DISTINCT name FROM a;

image.png

  • In table a, query the id, name, and num columns, divide the num column values by 2, and return the modified the num column as a column named avg.

SELECT id, name, num/2 AS avg FROM a;

image.png

  • In table a, find the rows in which the value for the name column is 'a'. Then, return the values at the intersections of this row and the id, name, and num columns.

SELECT id, name, num FROM a WHERE name = 'a';

image.png

  • In table a, query the id and name columns, and group the num column values by name. Then, return the sum of the num column values in each group.

SELECT id, name, SUM(num) FROM a GROUP BY name;

image.png

  • In table a, query the id and name columns, group the num column values by name, and calculate the sum of the num column values in each group. Then, return the sum value that is less than 160.

SELECT id, name, SUM(num) as sum FROM a GROUP BY name HAVING SUM(num) < 160;

image.png

  • In table a, query the id, name, and num columns. Then, sort the data by the num column in ascending order and return the result set.

SELECT * FROM a ORDER BY num ASC;

image.png

  • In table a, query the id, name, and num columns. Then, sort the data by the num column in descending order and return the result set.

SELECT * FROM a ORDER BY num DESC;

image.png

SELECT statements that contain set operators

Description

You can execute the SELECT statement that contains the UNION, MINUS, or INTERSECT operator to combine query results.

Syntax

select_clause_set:
  simple_select [ UNION | UNION ALL |  | INTERSECT] select_clause_set_right
  [ORDER BY sort_list_columns]

select_clause_set_right:
  simple_select | 
  select_caluse_set

Parameters

Parameter

Description

UNION ALL

Combine the results of two queries and return all rows.

UNION

Combine the results of two queries and return distinct rows.

MINUS

Return the distinct rows in the left query that are not selected by the right query.

INTERSECT

Return the distinct rows that are selected by the left and right queries.

Examples

The following examples are based on tables t1 and t2.

create table t1 (c1 int, c2 int);
create table t2 (c1 int, c2 int);
insert into t1 values (1, -1), (2, -2);
insert into t2 values (1, 1), (2, -2), (3, 3);
  • Query all rows from t1 and t2.

SELECT C1, C2 FROM T1 UNION ALL SELECT C1, C2 FROM T2;
+------+------+
| C1   | C2   |
+------+------+
|    1 |   -1 |
|    2 |   -2 |
|    1 |    1 |
|    2 |   -2 |
|    3 |    3 |
+------+------+
  • Query all distinct rows from t1 and t2.

SELECT C1, C2 FROM T1 UNION SELECT C1, C2 FROM T2;
+------+------+
| C1   | C2   |
+------+------+
|    1 |   -1 |
|    2 |   -2 |
|    1 |    1 |
|    3 |    3 |
+------+------+
  • Query the rows that exist in t1 and t2.

SELECT C1, C2 FROM T1 INTERSECT SELECT C1, C2 FROM T2;
+------+------+
| C1   | C2   |
+------+------+
|    2 |   -2 |
+------+------+
  • Query the rows that exist in t1 but do not exist in t2.

SELECT C1, C2 FROM T1 MINUS SELECT C1, C2 FROM T2;
+------+------+
| C1   | C2   |
+------+------+
|    1 |   -1 |
+------+------+

SELECT statements that contain WITH clauses

Description

You can execute the SELECT statement that contains a WITH clause to reduce duplicate subqueries. Subqueries in WITH clauses are used as common expressions that can be referenced by each query.

Syntax

with_clause_select:
    with_clause simple_select

with_clause:
    WITH table_name [opt_column_alias_name_list] AS ( select_clause )

select_clause:
    simple_select | select_clause_set
  
opt_column_alias_name_list:
    (column_name_list)
  
column_name_list:
    column_name | column_name , column_name_list

Parameters

None

Examples

  • The following examples are based on tables t1 and t2. Execute the following SELECT statement to query t1 and t2:

create table t1(c1 int, c2 int, c3 int);
create table t2(c1 int);
insert into t1 values(1,1,1);
insert into t1 values(2,2,2);
insert into t1 values(3,3,3);
insert into t2 values(4);

select * from t1 where c1 > (select count(*) from t2)
                      and c2 > (select count(*) from t2)
                      and c3 > (select count(*) from t2);
+------+------+------+
| C1   | C2   | C3   |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+

Extract duplicate subqueries from the preceding SELECT statement, and add the subquery to a WITH clause.

with temp(cnt) as (select count(*) from t2)
select t1.* from t1, temp where c1 > temp.cnt and c2 > temp.cnt and c3 > temp.cnt;
+------+------+------+
| C1   | C2   | C3   |
+------+------+------+
|    2 |    2 |    2 |
|    3 |    3 |    3 |
+------+------+------+