SELECT statement|V4.2.0|OceanBase Database| docs|Distributed Database

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]]
        [LOCK IN SHARE MODE];
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
    | table_name@dblink_name

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	A filter condition, which is optional. 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_conditions	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 partitions from which data is selected in the table. For example, you can set this parameter to `PARTITION(p0,p1…)`.
dblink_name	The DBLink used to query data from tables in a remote database.
LOCK IN SHARE MODE	Locks query results.

Examples

Simple table queries

Take table a as an example.

Query the data in the name column from table a.

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

Deduplicate the query results of the name column.

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 corresponding values of the id, name, and num columns 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;
```

Use the LOCK IN SHARE MODE option to lock the query results of the a table.

obclient> SELECT * FROM a LOCK IN SHARE MODE;
+------+------+------+
| id   | name | num  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |  50 |
+------+------+------+
3 row in set

Queries using a DBLink

Read the number_t table in the remote test database.

obclient> SELECT * FROM number_t@ob_dblink;
+--------+-------------+---------+---------------------+--------------------------------+
| c_int  | c_bigint    | c_float |    c_double         |   c_decimal                    |
+--------+-------------+---------+---------------------+--------------------------------+
| 214748 | 92233720368 | 3.40282 | 1.7976931348623157  | 123456789123456789.1234567890  |
+--------+-------------+---------+---------------------+--------------------------------+
1 row in set

Read the number_t table in the remote test database and join the table with the local number_t table.

obclient> SELECT a.c_decimal, b.c_double FROM number_t a, number_t@ob_dblink b where a.c_int = b.c_int;
+--------------------------------+---------------------+
|   c_decimal                    |    c_double         |
+---------------------+--------------------------------+
| 123456789123456789.1234567890  |  1.7976931348623157 |
+---------------------+--------------------------------+
1 row in set

Read the datetime_t table in the remote mysql database.

obclient> SELECT * FROM mysql.datetime_t@ob_dblink;
+-------------+------------+---------+----------------------+----------------------+
| c_date      | c_time     | c_year  | c_datetime           |  c_timestamp         |
+-------------+------------+---------+-----------------------+---------------------+
|  2023-04-13 | 12:12:12   |  2078   | 2100-11-01 12:12:13  | 2100-12-01 21:14:15  |
+-------------+------------+---------+----------------------+----------------------+
1 row in set

Customer Stories

Documentation

SELECT statement