SIMPLE SELECT|V4.2.2| docs|Distributed Database

SIMPLE SELECT

Last Updated：2025-12-02 02:50:56 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 instance of each row that meets the condition is included in the result set. `DISTINCT` and `UNIQUE` are synonyms. If you specify `ALL`, all rows that meet the condition are included in the result set. The default value is `ALL`.
select_expr_list	Specifies the expressions or column names from the database that you want to query, separated by commas. Use `\` to indicate all columns. `table_name.`: specifies to select all columns from the specified table or view. `table_alias_name.`: specifies the alias of the table or view. `expr [[AS] column_alias_name]`: specifies the alias of the query column or expression. `AS` 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 data is to be selected.
joined_table	Specifies the join type for multi-table queries. `[INNER] JOIN` specifies an inner join. The optional `INNER` keyword can be omitted. Only data that meets the join condition is returned. `[OUTER] JOIN` specifies an outer join. The optional `OUTER` keyword can be omitted. `LEFT [OUTER]` specifies a left outer join. All common columns of the left table are returned. `RIGHT [OUTER]` specifies a right outer join. All common columns of the right table are returned. `FULL [OUTER]` specifies a full outer join. In addition to the inner join, rows not included 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 fields for grouping, which are usually used in conjunction with aggregate functions. Notice If there is no column with an aggregate function in the `SELECT` clause, the column specified in the `SELECT` clause must appear in the `GROUP BY` clause in a grouping query.
ROLLUP group_expression_list	Merges the grouping and generates statistical values.
CUBE group_expression_list	Generates groups by aggregating groups created based on the unique permutations of all items in the expression list, and merges the `GROUP BY` groups, to generate statistical values. Notice: The items in `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`, `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 groups increases exponentially with the number of items in the `CUBE` expression list, which means the number of groups is the exponential function of the number of items. Therefore, it is not recommended to use an expression 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 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 rows and thereby the consistency of the results.
OFFSET	The number of rows to skip before the first row of the pagination result set. The value of 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 value of `offset` 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 of `offset` contains a fraction, the fractional part of the value is truncated. If this clause is not specified, the value of `offset` is 0, and the first row is the first row of the pagination result set.
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`-th row are returned.
FIRST \| NEXT	The number or percentage of first rows or the number or percentage of next rows to be returned.
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 can be evaluated to a numeric value. If a negative value is specified, the value of `rowcount` is treated as 0. If `rowcount` is greater than the number of rows available after the `rowcount`-th row, all available rows are returned. If the value of `rowcount` contains a fraction, the fractional part of the value 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 can be evaluated to a numeric value. If a negative value is specified, the value of `percent` 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 provided. If the `ORDER BY` clause is not provided, no other rows are returned.
FOR UPDATE	Places an exclusive lock 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 to lock only certain tables (tables where the `column` specified in the clause resides). `WAIT`: after the specified `Integer` time, an attempt is made to lock the rows in the result set. If any row in the result set is already locked by another session, the attempt fails. `NOWAIT`: an attempt is made immediately to lock the rows in the result set. If any row in the result set is already locked by another session, the attempt fails. `SKIP LOCKED`: if any row in the result set is locked by another session, this row is skipped and not returned. Notice: The `SKIP LOCKED` clause is not supported for locks on rows of tables involved in joins.
pivot_clause	A clause that rotates rows into columns.
aggregate_function	The aggregate function.
expr	The expression that results in a constant value. The `pivot_in_clause` 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 object in the target database (OceanBase Database or Oracle Database), from which to access sequence values. You can use the sequence object to calculate the `NEXTVAL` or `CURRVAL` of the sequence.

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. 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 name column and in ascending order by 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 by using the FOR UPDATE clause.

/* Query and lock the row that has 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 that have 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 and count the number of rows that are grouped by the name and num columns from the tbl1 table.

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 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 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 the local database and the remote database*/
obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;

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

Perform a paginated query.

Query the employees with the 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 lowest empno values.

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

Query the employees who have the lowest wages and are in the top 25% of all employees.

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 employees who have the lowest wages and are in the top 25% of all employees, and all other employees who have the same wage 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 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