SIMPLE SELECT|V4.3.0| docs|Distributed Database

SIMPLE SELECT

Last Updated：2025-12-02 08:18:10 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 set may contain duplicate rows. If you specify `DISTINCT` or `UNIQUE`, only one row for each duplicate group is included in the query result set. `DISTINCT` and `UNIQUE` are synonyms. If you specify `ALL`, all rows are included in the query result set. 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 all columns of the specified table or view. `table_alias_name.`: indicates the alias of a table or view. `expr [[AS] column_alias_name]`: indicates the alias of a query column or expression. The `AS` keyword is optional.
FROM table_references	Specifies the 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 for the object from which to select data.
joined_table	Specifies the join type for multi-table queries. `[INNER] JOIN`: an inner join. The `INNER` keyword is optional. Only data that meets the join condition is returned. `[OUTER] JOIN`: an outer join. The `OUTER` keyword 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. In addition to the inner join, rows that are not returned in the inner join result set from the two tables are retained and supplemented 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 included in the query result set. `where_conditions` is an expression.
GROUP BY group_by_list	Specifies the grouping fields. Generally, grouping is used in conjunction with aggregate functions. Notice If no aggregate function is used in the `SELECT` clause, the column specified in the `SELECT` clause must appear in the `GROUP BY` clause.
ROLLUP group_expression_list	Merges the grouping sets and generates statistical values.
CUBE group_expression_list	Generates grouping sets based on all permutations of the items in the expression list, and merges the grouping sets and the `GROUP BY` sets to generate statistical values. Notice: 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 total counts of the `CUBE` expression increases exponentially with the number of items in the `CUBE` expression list (namely, the number of grouping sets). Therefore, we do not recommend that you use `CUBE` in scenarios with a large number of items.
GROUPING SETS group_expression_list	Specifies multiple data groups in one query and generates statistical values for the specified groups. The statistical values of the specified groups are aggregated and displayed. `GROUPING SETS` can contain single fields or field lists.
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 the columns and the sorting order (`ASC` or `DESC`) for sorting the result set. By default, the sorting order 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 paging. 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 and thus the consistency of the result set.
OFFSET	The number of rows to skip before the first row of the query result set is returned. The value of the `OFFSET` clause must be a numeric value or an expression that evaluates to a numeric value. If a negative value is specified, the value is treated as 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 value contains a fraction, the fraction is truncated. If this clause is not specified, the offset is 0 and paging starts from the first row.
ROW \| ROWS	Specifies a single row or multiple rows. Choose the keyword that ensures 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 the first returned rows or the next returned rows.
rowcount \| percent PERCENT	Use `rowcount` to specify the number of rows to be returned. The value of `rowcount` must be a numeric value or an expression that evaluates to a numeric value. If a negative value is specified, the value is treated as 0. If `rowcount` is greater than the number of available rows starting from `rowcount`+1, all available rows are returned. If the value contains a fraction, the fraction is truncated. If `rowcount` is `NULL`, 0 rows are returned. Use `percent PERCENT` to specify the percentage of the total number of rows. The value of `percent` must be a numeric value or an expression that evaluates to a numeric value. If a negative value is specified, the value is treated as 0. If `percent` is `NULL`, 0 rows are returned. By default, 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	A lock is placed on all rows in the result set to prevent concurrent modifications of the rows by other transactions or concurrent reads of the rows in some transaction isolation levels. `OF column`: in a multi-table join scenario, you can use this clause to specify that locks be placed only on the result rows of some tables, namely, the tables where the `column` specified in the clause is located. `WAIT`: the system attempts to lock the result rows after waiting for `Integer` seconds. If any row that needs to be locked is already locked by another session, the attempt fails. `NOWAIT`: the system attempts to lock the result rows immediately. If any row that needs to be locked is already locked by another session, the attempt fails. `SKIP LOCKED`: if a row is locked by another session, this row is skipped and not included in the query result set. Notice: The `SKIP LOCKED` clause is not supported for locks on rows resulted from multi-table joins.
pivot_clause	A clause that rotates rows into columns.
aggregate_function	The aggregate function.
expr	An expression that evaluates to a constant value. The `pivot_in_clause` supports only constant expressions.
unpivot_clause	A 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. You can use it to access the `NEXTVAL` or `CURRVAL` of the `SEQUENCE` object in the target database, which can be 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

Deduplicate the data of the name column read from the tbl1 table.

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 in a column named 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. Order the query results by the values in the num column in ascending order.

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. Order the query results by the name column in descending order and the num column in ascending order.

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 using the FOR UPDATE clause.

/* In session 1, query and lock the row where id=1 from the tbl1 table. */
obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE;
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | a    |  100 |
+------+------+------+
1 row in set

/* In session 2, query and lock the rows where id=1 or id=2 from the tbl1 table. */
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 that contains 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 by grouping the data in the tbl1 table by the name and num columns and count 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

Pivot 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

/* Pivot the rows 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 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 local and remote databases*/
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 empno 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 empno values and all other employees with the same salary 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 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 your 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