SIMPLE SELECT|V4.3.1| docs|Distributed Database

SIMPLE SELECT

Last Updated：2025-12-04 03:08:32 Updated

The syntax of the SELECT statement is complex. This topic describes the SIMPLE SELECT syntax.

Description

This statement is used to query data from a table or view.

Syntax

simple_select:
    SELECT [ hint_options ] [ DISTINCT | UNIQUE | ALL] select_expr_list
    FROM from_list
    [WHERE condition]
    [GROUP BY group_expression_list
        [{ROLLUP | CUBE | GROUPING SETS} group_expression_list]
        [HAVING condition]
     ]
    [ORDER BY order_expression_list]
    [FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
    [row_limiting_clause ]

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]
    | sequence_name.{ CURRVAL|NEXTVAL }@dblink_name

from_list:
    table_reference [, table_reference...]

table_reference:
    simple_table
    | joined_table
    | pivot_clause
    | unpivot_clause
    | table_name@dblink_name


simple_table:
    (table_factor [partition_option])[table_alias_name]
    | (select_stmt)  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]

row_limiting_clause:
    [ OFFSET offset { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
        { ROW | ROWS } { ONLY | WITH TIES } ]

pivot_clause:
    PIVOT
    (aggregate_function ( expr ) [[AS] alias ]
      [, aggregate_function ( expr ) [[AS] alias ]... ]
     pivot_for_clause
     pivot_in_clause
    )

pivot_for_clause:
    FOR { column| ( column [, column... ]) }

pivot_in_clause
    IN
    ( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
       [, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
     )

unpivot_clause :
    UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
    ( { column | ( column [, column... ]) }
     pivot_for_clause
     unpivot_in_clause
     )

unpivot_in_clause:
    IN
    ( { column | ( column [, column... ]) }[ AS { literal | ( literal [, literal... ]) } ]
        [, { column | ( column [, column... ] ) }[ AS {literal | ( literal [, literal... ]) } ]]
    )

Parameters

Parameter	Description
hint_options	Specifies hint options. It is optional.
DISTINCT \| UNIQUE \| ALL	The query result may contain duplicate rows. If you specify `DISTINCT` or `UNIQUE`, only one row is returned for each identical row in the query result. `DISTINCT` and `UNIQUE` are synonyms. If you specify `ALL`, all rows are returned. The default value is `ALL`.
select_expr_list	Specifies the expressions or column names from the database to be queried, separated by commas. Use `\` to indicate all columns. `table_name.`: indicates to select all columns from the specified table or view. `table_alias_name.`: indicates to use the alias name of the table or view. `expr [[AS] column_alias_name]`: indicates to alias the query column or expression. `AS` is optional.
FROM table_references	Specifies objects from which to select data.
PARTITION(partition_list)	Specifies the partitions of the table to be queried. For example: `partition(p0,p1...)`.
table_factor	Specifies a table name (base table or updatable view) or a subquery. You can directly query a function.
table_alias_name	Specifies an alias name for the object from which to select data.
joined_table	Specifies the join type for multi-table queries. `[INNER] JOIN`: an inner join. `INNER` is optional. Only data that meets the join condition is returned. `[OUTER] JOIN`: an outer join. `OUTER` is optional. `LEFT [OUTER]`: a left outer join. All common columns of the left table are returned. `RIGHT [OUTER]`: a right outer join. All common columns of the right table are returned. `FULL [OUTER]`: a full outer join. Apart from the inner join, rows not included in the join result are retained from both tables and extended with null values.
ON expression	Specifies the join condition for multi-table joins.
WHERE where_conditions	Specifies the filter condition. Only data that meets the condition is returned. The `where_conditions` parameter is an expression.
GROUP BY group_by_list	Specifies the fields for grouping, which are usually used in conjunction with aggregate functions. Note If there is no column with an aggregate function in the `SELECT` clause, the column specified in the `SELECT` clause must be included in the `GROUP BY` clause in a grouping query.
ROLLUP group_expression_list	Merges the grouping and generates statistical values.
CUBE group_expression_list	Aggregates data based on the groups generated from all permutations of the items in the expression list and merges the grouping, generating statistical values. Note: The items in the `select_expr_list` must also appear in the `CUBE expression_list`. You can specify multiple `CUBE` extensions and multiple occurrences of other `GROUP BY` extensions (`ROLLUP`, `CUBE`, and `GROUPING SETS`) in the `GROUP BY` clause. The syntax is `SELECT select_expr_list FROM ... GROUP BY [..., ] CUBE (group_expression_list[, group_expression_list...]) [, ...]`. If the `ORDER BY` clause is not specified, the order of the result set cannot be guaranteed. The number of grouping levels or the total number of groups increases exponentially with the number of items in the `CUBE expression_list`, that is, the number of groups is the exponential function of the number of items. Therefore, we do not recommend that you specify more than three items in the `CUBE expression_list`.
GROUPING SETS group_expression_list	Specifies multiple data groupings in one query, generates statistical values for the groupings, and displays the statistical values of the groupings aggregated. You can specify a field or a field list in the `GROUPING SETS` clause.
HAVING search_confitions	Filters the groups. The `HAVING` clause is similar to the `WHERE` clause, but the `HAVING` clause can be used with cumulative functions (`SUM`, `AVG`, etc.).
ORDER BY order_list	Specifies to display the query result set sorted by one or more columns in ascending (`ASC`) or descending (`DESC`) order. The default value is `ASC`. `ASC`: indicates to sort in ascending order. `DESC`: indicates to sort in descending order.
row_limiting_clause	Limits the number of rows returned by the query to implement pagination. You can specify the offset, the number, or the percentage of rows to be returned. You can add the `ORDER BY` clause to ensure the sorting order of the offset, thus ensuring consistent results.
OFFSET	The number of rows to be skipped before the first row of the query result is returned. The `offset` parameter must be a numeric value or an expression that can be evaluated to a numeric value. If a negative value is specified, the `offset` parameter is considered to be 0. If `NULL` or a value greater than or equal to the total number of rows to be returned is specified, 0 rows are returned. If the `offset` parameter contains a fractional part, the fractional part is truncated. If this clause is not specified, the offset is 0, and the first row is returned.
ROW \| ROWS	Specifies a single row or multiple rows. Choose the keyword that can ensure clear semantics.
FETCH	The number or percentage of rows to be returned. If this clause is not specified, all rows after the `offset` are returned.
FIRST \| NEXT	The number or percentage of first returned rows or the number or percentage of next returned rows.
rowcount \| percent PERCENT	Use `rowcount` to specify the number of rows to be returned. The `rowcount` parameter must be a numeric value or an expression that can be evaluated to a numeric value. If a negative value is specified, the `rowcount` parameter is considered to be 0. If `rowcount` is greater than the number of available rows starting from `rowcount`+1, all available rows are returned. If the `rowcount` parameter contains a fractional part, the fractional part is truncated. If `rowcount` is `NULL`, 0 rows are returned. Use `percent PERCENT` to specify the percentage of the total number of rows. The `percent` parameter must be a numeric value or an expression that can be evaluated to a numeric value. If a negative value is specified, the `percent` parameter is considered to be 0. If `percent` is `NULL`, 0 rows are returned. If `rowcount` or percent``PERCENT is not specified, 1 row is returned.
ONLY \| WITH TIES	Specifies to return the specified number or percentage of rows or to return other rows that have the same sorting key as the last row returned. If you specify `WITH TIES`, the `ORDER BY` clause must be added. If the `ORDER BY` clause is not specified, other rows are not returned.
FOR UPDATE	Locks all rows in the query result to prevent concurrent modifications of other transactions or concurrent reads in some transaction isolation levels. `OF column`: in a multi-table join scenario, you can use this clause to specify to lock only certain tables (tables where the `column` specified in the clause resides). `WAIT`: waits for `Integer` time units before attempting to lock the query result rows. If a row is already locked by another session, the attempt fails. `NOWAIT`: immediately attempts to lock the query result rows. If a row is already locked by another session, the attempt fails. `SKIP LOCKED`: if a row in the query result is already locked by another session, the row is skipped and not locked, and an unlocked row is returned instead. Note: `SKIP LOCKED` is not supported in multi-table join locking scenarios.
pivot_clause	The clause that rotates rows into columns.
aggregate_function	The aggregate function.
expr	The expression that results in a constant value. The `pivot_in_clause` parameter supports only constant expressions.
unpivot_clause	The clause that rotates columns into rows.
dblink_name	The name of the database link (DBLINK) to the target database.
sequence_name	The name of the sequence object in the target database. You can use the DBLINK to access the `NEXTVAL` or `CURRVAL` of the sequence object in an OceanBase database or an Oracle database.

Examples

Read the name data from the tbl1 table.

obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM tbl1;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

obclient> SELECT name FROM tbl1;
+------+
| NAME |
+------+
| a    |
| b    |
| a    |
+------+
3 rows in set

Read the name data from the tbl1 table and remove the duplicates.

obclient> SELECT DISTINCT name FROM tbl1;
+------+
| NAME |
+------+
| a    |
| b    |
+------+
2 rows in set

Query the id, name, and num columns from the tbl1 table. Then divide the values in the num column by 2 and output the results with the column name avg.

obclient> SELECT id, name, num/2 AS avg FROM tbl1;
+------+------+------+
| ID   | NAME | AVG  |
+------+------+------+
|    1 | a    |   50 |
|    2 | b    |  100 |
|    3 | a    |   25 |
+------+------+------+
3 rows in set

Return the values in the id, name, and num columns from the tbl1 table that meet the filter condition name = 'a'.

obclient> SELECT id, name, num FROM tbl1 WHERE name = 'a';
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    3 | a    |   50 |
+------+------+------+
2 rows in set

Query the name column from the tbl1 table. Group the data by the name column and sum the values in the num column. Return only the rows where the sum is less than 160.

obclient> SELECT name,SUM(num)  sum
                FROM tbl1
                GROUP BY name
                HAVING SUM(num) < 160;
+------+------+
| NAME | SUM  |
+------+------+
| a    |  150 |
+------+------+
1 row in set

Query the id, name, and num columns from the tbl1 table. Sort the query results in ascending order by the values in the num column.

obclient> SELECT * FROM tbl1 ORDER BY num ASC;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    3 | a    |   50 |
|    1 | a    |  100 |
|    2 | b    |  200 |
+------+------+------+
3 rows in set

Query all columns from the tbl1 table. Sort the query results in descending order by the values in the name column and in ascending order by the values in the num column.

obclient> SELECT * FROM tbl1 ORDER BY name DESC,num ASC;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    2 | b    |  200 |
|    3 | a    |   50 |
|    1 | a    |  100 |
+------+------+------+
3 rows in set

Query the rows that have the specified id from the tbl1 table and lock the query results with the FOR UPDATE clause.

/* Query and lock the row with the ID of 1 from the tbl1 table in session 1 */
obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
+------+------+------+
1 row in set

/* Query and lock the rows with the IDs of 1 and 2 from the tbl1 table in session 2 */
obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE;
ORA-30006: resource busy; acquire with WAIT timeout expired

obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    2 | b    |  200 |
+------+------+------+
1 row in set

Create a table named group_tbl1 and insert data. Execute a GROUP BY query statement with the CUBE operator.

obclient> CREATE TABLE group_tbl1 (group_id INT,job VARCHAR2(10),name VARCHAR2(10),salary INT);
Query OK, 0 rows affected
obclient> INSERT INTO group_tbl1 VALUES(10,'Coding','Bruce',1000),
    (10,'Programmer','Clair',1000),
    (20,'Coding','Jason',2000),
    (20,'Programmer','Joey',2000),
    (30,'Coding','Rebecca',3000),
    (30,'Programmer','Rex',3000);
Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 0
 obclient> SELECT * FROM group_tbl1;
 +----------+------------+---------+--------+
 | GROUP_ID | JOB        | NAME    | SALARY |
 +----------+------------+---------+--------+
 |       10 | Coding     | Bruce   |   1000 |
 |       10 | Programmer | Clair   |   1000 |
 |       20 | Coding     | Jason   |   2000 |
 |       20 | Programmer | Joey    |   2000 |
 |       30 | Coding     | Rebecca |   3000 |
 |       30 | Programmer | Rex     |   3000 |
 +----------+------------+---------+--------+
 6 rows in set
 obclient> SELECT group_id, salary, SUM(salary) FROM group_tbl1 GROUP BY CUBE (group_id, salary);
 +----------+--------+-------------+
 | GROUP_ID | SALARY | SUM(SALARY) |
 +----------+--------+-------------+
 |     NULL |   NULL |       12000 |
 |     NULL |   1000 |        2000 |
 |     NULL |   2000 |        4000 |
 |     NULL |   3000 |        6000 |
 |       10 |   NULL |        2000 |
 |       20 |   NULL |        4000 |
 |       30 |   NULL |        6000 |
 |       10 |   1000 |        2000 |
 |       20 |   2000 |        4000 |
 |       30 |   3000 |        6000 |
 +----------+--------+-------------+
 10 rows in set

Query data from the tbl1 table by grouping the data in the table by the name and num columns and counting the number of rows in each group.

obclient> SELECT name, num, COUNT(*) from tbl1 GROUP BY GROUPING SETS(name, num);
+------+------+----------+
| NAME | NUM  | COUNT(*) |
+------+------+----------+
| a    | NULL |        2 |
| b    | NULL |        1 |
| NULL |  100 |        1 |
| NULL |  200 |        1 |
| NULL |   50 |        1 |
+------+------+----------+
5 rows in set

Convert the rows in the emp_phone table into columns and then convert the columns back into rows.

obclient> CREATE TABLE emp(name VARCHAR2(50), num CHAR, phone VARCHAR2(50));
Query OK, 0 rows affected

obclient> INSERT INTO emp VALUES('ZhangSan', '1', '1234-5678'),('ZhangSan', '2', '3219-6066'),('ZhangSan', '3', '5365-9583');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM emp;
+----------+------+-----------+
| NAME     | NUM  | PHONE     |
+----------+------+-----------+
| ZhangSan | 1    | 1234-5678 |
| ZhangSan | 2    | 3219-6066 |
| ZhangSan | 3    | 5365-9583 |
+----------+------+-----------+
3 rows in set

/* Convert the rows in the emp table into columns */
obclient> SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
+----------+-----------+-----------+-----------+
| NAME     | HOME      | OFFICE    | MOBILE    |
+----------+-----------+-----------+-----------+
| ZhangSan | 1234-5678 | 3219-6066 | 5365-9583 |
+----------+-----------+-----------+-----------+
1 row in set

/* Convert the columns in the emp table back into rows */
obclient> CREATE VIEW v_emp AS SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
Query OK, 0 rows affected

obclient>  SELECT * FROM v_emp UNPIVOT(phone FOR num IN (home AS 1, office AS 2, mobile AS 3));
+----------+-----+-----------+
| NAME     | NUM | PHONE     |
+----------+-----+-----------+
| ZhangSan |   1 | 1234-5678 |
| ZhangSan |   2 | 3219-6066 |
| ZhangSan |   3 | 5365-9583 |
+----------+-----+-----------+
3 rows in set

Query data from a table in a remote database.

/*Query data from a table in OceanBase Database*/
obclient> SELECT ID FROM tbl2@ob_dblink;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set

obclient> SELECT * FROM tbl2@ob_dblink;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

/*Query data from a table in Oracle Database*/
obclient> SELECT ID FROM tbl2@ora_dblink;
+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set

obclient> SELECT * FROM tbl2@ora_dblink;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
|    2 | b    |  200 |
|    3 | a    |   50 |
+------+------+------+
3 rows in set

/*Query data from both a local database and a remote database*/
obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;

/*Query data from tables in different remote databases*/
obclient> SELECT * FROM tbl2@ob_dblink t_remote1,tbl2@ora_dblink t_remote2 WHERE t_remote1.col1 = t_remote2.col1;

Paginate query results

Query the employees with the three lowest empno values.

obclient> CREATE TABLE emp(  
    empno         NUMBER(4,0),  
    empname       VARCHAR(10),  
    job           VARCHAR(9),  
    mgr           NUMBER(4,0),  
    hiredate      DATE,  
    sal           NUMBER(7,2),  
    comm          NUMBER(7,2),        
    deptno        NUMBER(2,0),   
    CONSTRAINT PK_emp PRIMARY KEY (empno)
);
Query OK, 0 rows affected

obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null,'17-DEC-81',5000,null,10)
   ,(1698,'BLAKE','MANAGER',1839,'01-MAY-81',2850,null,30)
   ,(1782,'CLARK', 'MANAGER', 1839, '09-JUN-81', 2450, null, 10)
   ,(1566,'JONES','MANAGER',1839, '02-APR-81',2975,null,40)
   ,(1788,'SCOTT','ANALYST',1566, '15-JUL-87',3000,null,20)
   ,(1902,'FORD','ANALYST',1566, '05-OCT-81',3000,null,40)
   ,(1369,'SMITH','CLERK',1902, '17-NOV-80',800,null,20)
   ,(1499,'ALLEN','SALESMAN',1698, '20-FEB-81',1600,300,30);

Query OK, 8 rows affected
Records: 8  Duplicates: 0  Warnings: 0

obclient> SELECT empno, empname FROM emp ORDER BY empno FETCH FIRST 3 ROWS ONLY;
+-------+---------+
| EMPNO | EMPNAME |
+-------+---------+
|  1369 | SMITH   |
|  1499 | ALLEN   |
|  1566 | JONES   |
+-------+---------+
3 rows in set

Query the employees with the next three lowest empno values.

obclient> SELECT empno, empname
FROM emp
ORDER BY empno
FETCH NEXT 3 ROWS ONLY;

Query the top 25% of employees with the lowest salary values.

obclient> SELECT empno, empname sal FROM emp ORDER BY sal
 FETCH FIRST 25 PERCENT ROWS ONLY;
+-------+-------+
| EMPNO | SAL   |
+-------+-------+
|  1499 | ALLEN |
|  1698 | BLAKE |
+-------+-------+
2 rows in set

Query the top 25% of employees with the lowest salary values and all other employees with the same salary value as the last employee obtained in the previous example.

obclient> SELECT empno, empname sal FROM emp ORDER BY sal
 FETCH FIRST 25 PERCENT ROWS WITH TIES;
+-------+-------+
| EMPNO | SAL   |
+-------+-------+
|  1499 | ALLEN |
|  1698 | BLAKE |
+-------+-------+
2 rows in set

Query data from the t1 table and specify a table alias.

obclient> CREATE TABLE t1 (c1 INT, c2 INT );
Query OK, 0 rows affected

obclient> INSERT INTO t1 VALUES ('1','2');
Query OK, 1 row affected

obclient> SELECT * FROM (t1) a;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    2 |
+------+------+
1 row in set

Generate a random number by using the RANDOM() function and query the GENERATOR() function directly after the FROM clause.

obclient> SELECT RANDOM(4) FROM GENERATOR(3);
+---------------------+
| RANDOM(4)           |
+---------------------+
| 5267436225003336391 |
| -851690886662571060 |
| 1738617244330437274 |
+---------------------+
3 rows in set

Access the sequence values in a remote database.

obclient> CREATE DATABASE LINK seq_link CONNECT TO test@oracle IDENTIFIED BY test HOST '127.xxx.xxx.xxx:2828';
Query OK, 0 rows affected

In the remote OceanBase database, create a sequence named my_seq.

obclient> CREATE SEQUENCE my_seq START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
Query OK, 0 rows affected

In the local database, access the sequence values in the remote OceanBase database.

obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL;
+---------+
| NEXTVAL |
+---------+
|       1 |
+---------+
1 row in set

obclient> SELECT my_seq.CURRVAL@seq_link FROM DUAL;
+---------+
| CURRVAL |
+---------+
|       1 |
+---------+
1 row in set

obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL;
+---------+
| NEXTVAL |
+---------+
|       3 |
+---------+
1 row in set