SELECT

Purpose

The statement is used to query data from one or more tables.

This topic describes the general syntax of the SELECT statement. For more information about other SELECT statements, see:

• JOIN clause
• WINDOW clause
• UNION clause
• SELECT INTO

Privilege requirements

To execute a SELECT statement, the current user must have the SELECT privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Syntax

select_stmt:
    SELECT [hint_options]
        [{DISTINCT | UNIQUE} | ALL]
        [SQL_CALC_FOUND_ROWS]
        [STRAIGHT_JOIN]
        select_expr_list
        [FROM from_list]
        [WHERE where_condition]
        [GROUP BY group_by_condition_list [WITH ROLLUP]
         | GROUP BY [group_by_condition_list] group_by_summary_option (expression_list)]
        [HAVING having_condition]
        [window_clause]
        [ORDER BY order_by_condition_list]
        [LIMIT limit_clause]
        [lock_option];

select_expr_list:
    select_expr [, select_expr ...]

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

from_list:
    DUAL
    | table_references

where_condition:
    expression

group_by_condition_list:
    group_by_condition [, group_by_condition ...]

group_by_condition:
    expression [ASC | DESC]

group_by_summary_option:
    GROUPING SETS
    | ROLLUP
    | CUBE

expression_list:
    expression [, expression ...]

having_condition:
    expression

order_by_condition_list:
    order_by_condition [, order_by_condition ...]

order_by_condition:
    expression [ASC | DESC]

limit_clause:
    [offset,] row_count
    | row_count OFFSET offset

lock_option:
    FOR UPDATE [opt_for_update_wait]
    | LOCK IN SHARE MODE

opt_for_update_wait:
    WAIT {decimal | intnum}
    | NOWAIT
    | NO_WAIT
    | SKIP LOCKED

Parameters

select_expr

select_expr: specifies the columns or expressions to be included in the query result. Separate multiple expressions or column names with commas (,). Valid values are as follows:

• *: indicates to select all columns.

  Here are some examples:

  1. Create tables tbl1 and tbl2.

     CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT)
       PARTITION BY HASH(col1)
       PARTITIONS 5;
     

     CREATE TABLE tbl2(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
     

  2. Insert test data into the tbl1 table.

     INSERT INTO tbl1 VALUES(1, 'A1', 1),(2, 'A2', 2),(3, 'A3', 3);
     

     INSERT INTO tbl2 VALUES(1, 'A1', 1),(2, 'A2', 22),(3, 'A3', 33);
     

  3. View the data in the tbl1 table.

     SELECT * FROM tbl1;
     

     The return result is as follows:

     +------+------+------+
     | col1 | col2 | col3 |
     +------+------+------+
     |    1 | A1   |    1 |
     |    2 | A2   |    2 |
     |    3 | A3   |    3 |
     +------+------+------+
     3 rows in set
     

• table_name.{* | column_name}: indicates to select all columns or a specific column in a table.

  • table_name.*: indicates to select all columns in a table.

  • table_name.column_name: indicates to select a specific column in a table.

    Here are some examples:

    Read the col1 data from the tbl1 table.

    SELECT tbl1.col1 FROM tbl1;
    

    The return result is as follows:

    +------+
    | col1 |
    +------+
    |    1 |
    |    2 |
    |    3 |
    +------+
    3 rows in set
    

• table_alias_name.{* | column_name}: similar to the preceding format, but uses a table alias when selecting columns.

• expr [[AS] column_alias_name]: indicates to select an expression and can specify an alias for the selected expression as the name of a column.

  • expr: indicates a column name, function, or calculation expression.

    • expr AS column_alias_name/expr column_alias_name: indicates to select an expression and name it as column_alias_name.

  Here are some examples:

  Read the col1 data from the tbl1 table, calculate the value of col1+1, and name the new column col1_add. Also, calculate col3*2 and name the new column col3_mul.

  SELECT col1, col1+1 AS col1_add, col3*2 col3_mul FROM tbl1;
  

  The return result is as follows:

  +------+----------+----------+
  | col1 | col1_add | col3_mul |
  +------+----------+----------+
  |    1 |        2 |        2 |
  |    2 |        3 |        4 |
  |    3 |        4 |        6 |
  +------+----------+----------+
  3 rows in set
  

from_list

• DUAL: indicates a virtual table name. It is usually used to perform some calculations or functions without actual tables.

  Here are some examples:

  SELECT 1+1, SYSDATE() FROM DUAL;
  

  The return result is as follows:

  +------+---------------------+
  | 1+1  | SYSDATE()           |
  +------+---------------------+
  |    2 | 2024-08-28 15:20:59 |
  +------+---------------------+
  1 row in set
  

• table_references: a list of table references, which can contain multiple table references separated by commas (,). This specifies the data source table list, which can be one or more tables. For more information about the syntax of table_references, see JOIN clause.

where_condition

expression: specifies the condition expression for filtering the rows to be updated.

Here are some examples:

Select all rows that meet the conditions col1 > 1 and col2 = 'A3' from the tbl1 table.

SELECT * FROM tbl1
  WHERE col1 > 1
  AND col2 = 'A3';

The return result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | A3   |    3 |
+------+------+------+
1 row in set

group_by_condition

expression [ASC | DESC]: specifies the condition expression for grouping. You can add ASC or DESC to control the order of groups in the result set after grouping.

Here are some examples:

Query the col1 and col2 columns from the tbl1 table, group the result set by the col2 column, sum up the col3 values in each group, and output the result.

SELECT col1, col2, SUM(col3)
  FROM tbl1
  GROUP BY col2 DESC;

The return result is as follows:

+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
|    3 | A3   |         3 |
|    2 | A2   |         2 |
|    1 | A1   |         1 |
+------+------+-----------+
3 rows in set

group_by_summary_option

• GROUPING SETS: specifies a custom grouping method that allows specifying multiple grouping combinations. It has the following characteristics:

  • Users can flexibly define the required grouping combinations.
  • The resulting dataset includes only the grouping combinations specified by the user.

  Note

  GROUP BY GROUPING SETS ((col1), (col2), (col3)) is equivalent to GROUP BY GROUPING SETS (col1, col2, col3).

  Here is an example:

  SELECT col1, col2, col3, COUNT(*)
  FROM tbl1
  GROUP BY GROUPING SETS ((col1), (col2), (col3));
  

  The return result set is as follows:

  +------+------+------+----------+
  | col1 | col2 | col3 | COUNT(*) |
  +------+------+------+----------+
  |    1 | NULL | NULL |        1 |
  |    2 | NULL | NULL |        1 |
  |    3 | NULL | NULL |        1 |
  | NULL | A1   | NULL |        1 |
  | NULL | A2   | NULL |        1 |
  | NULL | A3   | NULL |        1 |
  | NULL | NULL |    1 |        1 |
  | NULL | NULL |    2 |        1 |
  | NULL | NULL |    3 |        1 |
  +------+------+------+----------+
  9 rows in set
  

• ROLLUP: specifies the hierarchical summarization method. It reduces the number of grouping columns step by step in the order of the grouping columns, generating multi-level summary results. It has the following characteristics:

  • Reduces grouping columns sequentially based on the order of columns in expression_list until only the grand total row remains.

  • The result set contains:

    • The detail data of each group.
    • The subtotal data of each group.
    • The total row.

  ROLLUP can be considered a special case of GROUPING SETS. For example:

  GROUP BY ROLLUP(col1, col2, col3)
  
  is equivalent to
  
  GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1), ())
  

  ROLLUP can also be combined with GROUPING SETS. For example:

  GROUP BY GROUPING SETS((col2), (col3), ROLLUP(col1, col2, col3))
  
  is equivalent to
  
  GROUP BY GROUPING SETS((col2), (col3), (col1, col2, col3), (col1, col2), (col1), ())
  

  Here is an example:

  SELECT col1, col2, col3, COUNT(*)
  FROM tbl1
  GROUP BY ROLLUP (col1, col2, col3);
  

  The return result set is as follows:

  +------+------+------+----------+
  | col1 | col2 | col3 | COUNT(*) |
  +------+------+------+----------+
  |    1 | A1   |    1 |        1 |
  |    1 | A1   | NULL |        1 |
  |    1 | NULL | NULL |        1 |
  |    2 | A2   |    2 |        1 |
  |    2 | A2   | NULL |        1 |
  |    2 | NULL | NULL |        1 |
  |    3 | A3   |    3 |        1 |
  |    3 | A3   | NULL |        1 |
  |    3 | NULL | NULL |        1 |
  | NULL | NULL | NULL |        3 |
  +------+------+------+----------+
  10 rows in set
  

• CUBE: specifies the multidimensional summarization method. It generates summaries of all possible groupings. It has the following characteristics:

  • The detail data of each group.
  • The summary data of all possible groupings.
  • The total row.

  CUBE can be considered a special case of GROUPING SETS. For example:

  GROUP BY CUBE(col1, col2, col3)
  
  is equivalent to
  
  GROUP BY GROUPING SETS ((col1, col2, col3), (col1, col2), (col1, col3), (col2, col3), (col1), (col2), (col3), ())
  

  CUBE can also be combined with GROUPING SETS. For example:

  GROUP BY col1, CUBE(col2, col3), GROUPING SETS((col4), (col5))
  
  is equivalent to
  
  GROUP BY GROUPING SETS(
    (col1, col2, col3, col4), (col1, col2, col3, col5),
    (col1, col2, col4), (col1, col2, col5),
    (col1, col3, col4), (col1, col3, col5),
    (col1, col4), (col1, col5))
  

  Here is an example:

  SELECT col1, col2, col3, COUNT(*)
  FROM tbl1
  GROUP BY CUBE (col1, col2, col3);
  

  The return result set is as follows:

  +------+------+------+----------+
  | col1 | col2 | col3 | COUNT(*) |
  +------+------+------+----------+
  | NULL | NULL | NULL |        3 |
  | NULL | NULL |    1 |        1 |
  | NULL | NULL |    2 |        1 |
  | NULL | NULL |    3 |        1 |
  | NULL | A1   | NULL |        1 |
  | NULL | A2   | NULL |        1 |
  | NULL | A3   | NULL |        1 |
  | NULL | A1   |    1 |        1 |
  | NULL | A2   |    2 |        1 |
  | NULL | A3   |    3 |        1 |
  |    1 | NULL | NULL |        1 |
  |    2 | NULL | NULL |        1 |
  |    3 | NULL | NULL |        1 |
  |    1 | NULL |    1 |        1 |
  |    2 | NULL |    2 |        1 |
  |    3 | NULL |    3 |        1 |
  |    1 | A1   | NULL |        1 |
  |    2 | A2   | NULL |        1 |
  |    3 | A3   | NULL |        1 |
  |    1 | A1   |    1 |        1 |
  |    2 | A2   |    2 |        1 |
  |    3 | A3   |    3 |        1 |
  +------+------+------+----------+
  22 rows in set
  

having_condition

expression: specifies the condition expression for filtering the grouped result set.

Here are some examples:

Query the col1 and col2 columns from the tbl1 table, group the result set by the col2 column, sum up the col3 values in each group, and output the rows where the sum is less than 3.

SELECT col1, col2, SUM(col3)
  FROM tbl1
  GROUP BY col2
  HAVING SUM(col3) < 3;

The return result is as follows:

+------+------+-----------+
| col1 | col2 | SUM(col3) |
+------+------+-----------+
|    1 | A1   |         1 |
|    2 | A2   |         2 |
+------+------+-----------+
2 rows in set

order_by_condition

expression [ASC | DESC]: specifies the condition expression for sorting the result set.

ASC | DESC: an optional parameter that specifies the sorting order. Valid values are ASC (default) and DESC.

Here are some examples:

Query the data from the tbl1 table and output the query result in descending order (DESC) of col3.

SELECT * FROM tbl1
  ORDER BY col3 DESC;

The return result is as follows:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | A3   |    3 |
|    2 | A2   |    2 |
|    1 | A1   |    1 |
+------+------+------+
3 rows in set

limit_clause

• [offset,] row_count: the parameters must be integer constants, which are described as follows:

  • offset: indicates the number of rows to skip from the start of the result set. The default initial record row offset is 0.
  • row_count: indicates the number of rows to return. If you specify only row_count, the first row_count rows of the result set are returned.

• row_count OFFSET offset: indicates to skip the first offset rows of the result set and return the next row_count rows.

Here are some examples:

Query the data from the tbl1 table and return two rows of the result set starting from the second row.

SELECT * FROM tbl1
  LIMIT 1, 2;

The return result is as follows:

code-placeholder/58898481-9460-462b-979e-74ec2ad3da3

lock_option

• FOR UPDATE [opt_for_update_wait]: indicates to add an exclusive lock to all rows in the query result to prevent concurrent modifications by other transactions or concurrent reads in some transaction isolation levels. For more information about exclusive locks, see Lock query results SELECT FOR UPDATE.

  • opt_for_update_wait: indicates the behavior when a lock is obtained. Valid values are as follows:

    • WAIT {decimal | intnum}: indicates to wait for the resources to be released by other transactions for the specified time. If the resources are not released within the specified time, the system returns an error indicating that the resources cannot be obtained. The unit of time is second.
    • NOWAIT/NO_WAIT: indicates not to wait for the resources to be released by other transactions and directly return an error indicating that the resources cannot be obtained.
    • SKIP LOCKED: indicates not to wait for the row locks to be obtained. The query is executed immediately, and the rows that are locked are removed from the result set.

  Here are some examples:

  • You can execute SELECT ... FOR UPDATE to add row-level locks to a table. If you use the LIMIT 1 clause, the optimizer will push the operator down to the table scan step and add locks only to the rows returned by LIMIT.

    SELECT * FROM tbl1
      LIMIT 1
      FOR UPDATE;
    

  • If you use the ORDER BY clause to sort the query result, the result is sorted first and then LIMIT 1 is executed. In this case, locks are added to all selected rows.

    SELECT * FROM tbl1
      ORDER BY col1
      LIMIT 1
      FOR UPDATE;
    

• LOCK IN SHARE MODE: indicates to obtain a shared lock when querying data to prevent other transactions from writing to the data but allow other transactions to read the data. For more information about shared locks, see Lock query results LOCK IN SHARE MODE.

Examples

Example of a simple table query

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1(col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   

2. Insert test data into the test_tbl1 table.

   INSERT INTO test_tbl1 VALUES (1, 'A1', 10),(2, 'A2', 15),(3, 'A1', 8);
   

3. View the data in the col2 column of the test_tbl1 table and remove duplicates.

   SELECT DISTINCT col2 FROM test_tbl1;
   

   The return result is as follows:

   +------+
   | col2 |
   +------+
   | A1   |
   | A2   |
   +------+
   2 rows in set
   

Example of querying data by using dblink

• Query data from the number_t table in the test database.

  SELECT * FROM number_t@ob_dblink;
  

  The return result is as follows:

  +--------+-------------+---------+---------------------+--------------------------------+
  | c_int  | c_bigint    | c_float |    c_double         |   c_decimal                    |
  +--------+-------------+---------+---------------------+--------------------------------+
  | 214748 | 92233720368 | 3.40282 | 1.7976931348623157  | 123456789123456789.1234567890  |
  +--------+-------------+---------+---------------------+--------------------------------+
  1 row in set
  

• Query data from the number_t table in the test database and join it with the local number_t table.

  SELECT a.c_decimal, b.c_double
    FROM number_t a, number_t@ob_dblink b
    WHERE a.c_int = b.c_int;
  

  The return result is as follows:

  +--------------------------------+---------------------+
  |   c_decimal                    |    c_double         |
  +---------------------+--------------------------------+
  | 123456789123456789.1234567890  |  1.7976931348623157 |
  +---------------------+--------------------------------+
  1 row in set
  

• Query data from the datetime_t table in the mysql database.

  SELECT * FROM mysql.datetime_t@ob_dblink;
  

  The return result is as follows:

  +-------------+------------+---------+----------------------+----------------------+
  | 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
  

References

Query a single table