SIMPLE SELECT|V4.2.1|OceanBase Database| docs|Distributed Database

SIMPLE SELECT

Last Updated：2026-07-16 07:49:27 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 of the table or view. `expr [[AS] column_alias_name]`: indicates to alias the 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), a subquery, or a direct query 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 column values of the left table are returned. `RIGHT [OUTER]`: a right outer join. All common column values of the right table are returned. `FULL [OUTER]`: a full outer join. In addition to the inner join, rows not included in the inner join result from both tables are retained 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. 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 results and generates statistical values.
CUBE group_expression_list	Generates groups by aggregating all permutations of the specified expressions and merges the grouping results generated by using the `GROUP BY` clause, and generates statistical values. Notice: The items specified in the `select_expr_list` parameter 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 is not guaranteed. The number of grouping levels or total groups is 2 to the `n`th power, where `n` is the number of items in the `CUBE` expression list. The number of groups increases exponentially with the number of items in the expression list. Therefore, it is not recommended to use the `CUBE` clause in scenarios with a large number of items.
GROUPING SETS group_expression_list	Specifies multiple data groups in one query, generates statistical values for the specified groups, and displays the statistical values of the specified groups. You can specify a field or a field list in the `GROUPING SETS` clause.
HAVING search_confitions	Filters the grouped data. 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 rows and thus the consistency of the results.
OFFSET	The number of rows to skip before the first row of the query result is returned. The `offset` parameter must be a numeric value or an expression that evaluates to a numeric value. If a negative value is specified, the `offset` parameter 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 `offset` parameter contains a fraction, the fractional part is truncated. If this parameter is not specified, the offset is 0 and the first row is the first row of the query result set.
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 parameter 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 the `rowcount` parameter to specify the number of rows to be returned. The `rowcount` parameter must be a numeric value or an expression that evaluates to a numeric value. If a negative value is specified, the `rowcount` parameter is treated as 0. If the specified value is greater than the actual number of available rows starting from the `rowcount`+1 row, all available rows are returned. If the specified value contains a fraction, the fractional part is truncated. If the `rowcount` parameter is `NULL`, 0 rows are returned. Use the `percent PERCENT` parameter to specify the percentage of the total number of rows. The `percent` parameter must be a numeric value or an expression that evaluates to a numeric value. If a negative value is specified, the `percent` parameter is treated as 0. If the `percent` parameter is `NULL`, 0 rows are returned. If the `rowcount` or percent``PERCENT parameter is not specified, 1 row is returned.
ONLY \| WITH TIES	Specifies to return the specified number or percentage of rows using the `ONLY` keyword or to return other rows that have the same sorting key as the previously returned row using the `WITH TIES` keyword. If you use the `WITH TIES` keyword, you must use the `ORDER BY` clause. If the `ORDER BY` clause is not used, rows that meet the preceding condition are not returned.
FOR UPDATE	Locks all rows in the query result set 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 the `OF column` clause to specify to lock only certain tables (tables where the `column` specified in the clause resides). `WAIT`: after the specified `Integer` time, the system attempts to lock the rows in the query result set. If any row is locked by another session, the attempt fails. `NOWAIT`: the system attempts to lock the rows in the query result set immediately. If any row is locked by another session, the attempt fails. `SKIP LOCKED`: if any row in the query result set is locked by another session, the row is skipped and not returned. Notice: The `SKIP LOCKED` clause is not supported in multi-table join scenarios.
pivot_clause	A clause that rotates rows into columns.
aggregate_function	The aggregate function.
expr	The expression that evaluates to a constant value. The `pivot_in_clause` parameter 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 object in the target database (OceanBase Database or Oracle Database). You can use the DBLINK to request the `NEXTVAL` or `CURRVAL` of the sequence object.

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 duplicate data.

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

Query the id, name, and num columns from the tbl1 table based on 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.

/* 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 or 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 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 from the tbl1 table by grouping the data 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

Pivot the rows in the emp_phone table into columns, and then transpose the columns 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 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

/* Transpose the columns 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 based on 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 based on the lowest empno values, and all other employees who have 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

Use the RANDOM() function to generate a random number and the FROM clause to query the GENERATOR() function.

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

OceanBase

Customer Stories

Documentation

SIMPLE SELECT

Description

Syntax

Parameters

Examples