SELECT|V4.1.0| docs|Distributed Database

SELECT

Last Updated：2023-07-28 02:55:42 Updated

The syntax of SELECT is complex. This topic describes the syntax of general SELECT statements and aggregate SELECT statements.

Purpose

You can use this statement to query data from a table.

Syntax

SELECT
        [/*+ hint statement */]
        [ALL | DISTINCT | UNIQUE | SQL_CALC_FOUND_ROWS]
        select_expr_list
        [FROM from_list [WHERE condition]]
        [GROUP BY group_expression_list [WITH ROLLUP] [HAVING condition]]
        [ORDER BY order_expression_list]
        [limit_clause]
        [FOR UPDATE [opt_for_update_wait]];

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

from_list:
      DUAL
    | table_reference [, table_reference ...]

table_reference:
      simple_table
    | joined_table

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

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

partition_option:
    PARTITION (partition_name_list)

sample_clause:
    SAMPLE [BLOCK] [ ALL | BASE | INCR] (sample_percent)

opt_seed:
    SEED(integer)

index_hint:
    {USE | FORCE | IGNORE} {KEY | INDEX}
       [FOR {JOIN | ORDER BY | GROUP BY}] (index_list)

index_list:
    index_name [, index_name ...]

partition_name_list:
    partition_name [, partition_name ...]

outer_join_type:
    [NATURAL]{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]

limit_clause:
  LIMIT {[offset,] row_count |row_count OFFSET offset}

opt_for_update_wait:
    WAIT { DECIMAL | INTNUM }
  | NOWAIT | NO_WAIT

Parameters

Parameter	Description
ALL \| DISTINCT \| UNIQUE \| SQL_CALC_FOUND_ROWS	A database table may contain duplicate values. `DISTINCT` \| `UNIQUE`: Only distinct rows are listed in the query result. `ALL`: All rows are listed in the query result. The default value is `ALL`. `SQL_CALC_FOUND_ROWS`: In a query with the `LIMIT` clause, all returned rows are recorded, and the `FOUND_ROWS()` function executed subsequently can obtain all the rows.
select_expr	The expressions or column names that you want to query. Multiple values must be separated with commas (,). You can also use an asterisk (*) to represent all columns.
AS othername	Renames the output fields.
FROM table_references	The table or tables from which data is to be retrieved (multi-table query is supported).
WHERE where_conditions	Optional. A filter condition. Only the data that meets the condition is included in the query result. `where_conditions` is an expression.
GROUP BY group_by_list	Summarizes data by class.
WITH ROLLUP	Summarizes groups to produce higher-level aggregations, also known as hyper-aggregations, and additional rows.
HAVING search_confitions	The `HAVING` clause is similar to the `WHERE` clause, but the `HAVING` clause can use an accumulation function such as `SUM` or `AVG`.
SAMPLE [BLOCK] [ ALL \| BASE \| INCR] (sample_percent)	Scans only some records. `BLOCK` indicates that random data blocks are scanned. By default, random rows are scanned. `ALL` indicates that all data is scanned. The default value is `ALL`. `BASE` indicates that baseline data is scanned. `INCR` indicates that incremental data is scanned. `sample_percent` indicates the sampling ratio, in %. It supports the `INTEGER` and `DECIMAL` data types.
SEED integer	The sampling seed. Value range: [0, 4294967295]. The same result is always returned for the same seed.
{USE \| FORCE \| IGNORE} {KEY \| INDEX} [FOR {JOIN \| ORDER BY \| GROUP BY}] (index_list)	Specifies whether to use the specified index for the query. `USE` indicates that an index is used for a specific operation. `FORCE` indicates that indexes are forcibly used for some operations. `IGNORE` indicates that no index is used for a specific operation. `FOR JOIN` indicates that indexes are joined. `FOR ORDER BY` indicates that indexes are sorted. `FOR GROUP BY` indicates that indexes are grouped.
SQL_CALC_FOUND_ROWS	Displays the query results in ascending (`ASC`) or descending (`DESC`) order. The default value is `ASC`.
[LIMIT {[offset,] row_count \|row_count OFFSET offset}]	Forces the `SELECT` statement to return the specified number of records. `LIMIT` supports one or two numeric arguments. The arguments must be integer constants. Given two arguments, the first argument specifies the offset of the first record row to be returned, and the second argument specifies the maximum number of record rows to be returned. The offset of the initial record row is 0. Given one argument, the argument specifies the maximum number of record rows to be returned, and the offset is 0.
FOR UPDATE	Imposes an exclusive lock on all the rows in the query results to prevent other concurrent transactions from changing or reading the rows in some transaction isolation levels. `WAIT`: indicates the time for waiting for other transactions to release resources, in seconds. If the time expires, the system returns a failure of obtaining resources. `NOWAIT`	`NO_WAIT`: indicates that the system does not wait for other transactions to release resources, but directly returns a failure of obtaining resources.
PARTITION(partition_list)	The partition information of the table to be queried. For example: `partition(p0,p1…)`

Examples

Take table a as an example.

Query the data in the name field from table a.

obclient> SELECT name FROM a;
+------+
| name |
+------+
| a    |
| b    |
| a    |
+------+

Deduplicate the query results of the name field.

obclient> SELECT DISTINCT name FROM a;
+------+
| name |
+------+
| a    |
| b    |
+------+

Query the id, name, and num values from table a, divide the num values by 2, and name the output column as avg.

obclient> SELECT id, name, num/2 AS avg FROM a;
+------+------+----------+
| id   | name | avg      |
+------+------+----------+
|    1 | a    |  50.0000 |
|    2 | b    | 100.0000 |
|    3 | a    |  25.0000 |
+------+------+----------+

Return the values of the corresponding id, name, and num fields based on the filter condition name = 'a' from table a.

obclient> SELECT id, name, num FROM a WHERE name = 'a';
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | a    |  100 |
|    3 | a    |   50 |
+------+------+------+

Query the id and name values from table a, calculate the sum of the num values by name, and return the calculation results.

obclient> SELECT id, name, SUM(num) FROM a GROUP BY name;
+------+------+----------+
| id   | name | SUM(num) |
+------+------+----------+
|    1 | a    |      150 |
|    2 | b    |      200 |
+------+------+----------+

Query the id and name values from table a, calculate the sum of the num values by name, and return the rows with a sum of less than 160.

obclient> SELECT id, name, SUM(num) as sum FROM a GROUP BY name HAVING SUM(num) < 160;
+------+------+------+
| id   | name | sum  |
+------+------+------+
|    1 | a    |  150 |
+------+------+------+

Query the id, name, and num values from table a, and sort the result set by the num column in ASC order.

obclient> SELECT * FROM a ORDER BY num ASC;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    3 | a    |   50 |
|    1 | a    |  100 |
|    2 | b    |  200 |
+------+------+------+

Query the id, name, and num values from table a, and sort the result set by the num column in DESC order.

obclient> SELECT * FROM a ORDER BY num DESC;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    2 | b    |  200 |
|    1 | a    |  100 |
|    3 | a    |   50 |
+------+------+------+

Query the id, name, and num values from table a, and use LIMIT to forcibly return two result rows starting from the second row.

obclient> SELECT * FROM a LIMIT 1,2;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+

You can use the SELECT ... FOR UPDATE statement to apply a row lock to a table. If you use the LIMIT 1 clause, the operator is pushed down to the table scan step during query optimization, and only the rows returned by LIMIT are locked.
```
obclient> SELECT * FROM a LIMIT 1 FOR UPDATE;
```
If you use the ORDER BY clause, the query results are sorted first, and the LIMIT 1 clause is executed. In this case, all the selected rows are locked.
```
obclient> SELECT * FROM a ORDER BY id LIMIT 1 FOR UPDATE;
```