SIMPLE SELECT|V4.2.0| docs|Distributed Database

SIMPLE SELECT

Last Updated：2025-12-04 05:37:52 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]
  
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 data. `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 the alias 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 query table. 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 a multi-table query. `[INNER] JOIN`: inner join. `INNER` is optional. Only data that meets the join condition is returned. `[OUTER] JOIN`: outer join. `OUTER` is optional. `LEFT [OUTER]`: left outer join. All common columns of the left table are returned. `RIGHT [OUTER]`: right outer join. All common columns of the right table are returned. `FULL [OUTER]`: full outer join. In addition to the inner join, rows not included in the join result from the two tables are retained and supplemented with null values.
ON expression	Specifies the join condition for a multi-table join.
WHERE where_conditions	Specifies the filter condition. Only data that meets the condition is returned. The `where_conditions` parameter is of the expression type.
GROUP BY group_by_list	Specifies the grouping fields. Generally, the aggregate function is used in conjunction with this parameter. Notice If no aggregate function is used in the `SELECT` clause, the column specified in the `SELECT` clause must be included in the `GROUP BY` clause.
ROLLUP group_expression_list	Merges the grouping sets and generates statistical values.
CUBE group_expression_list	Generates groups for statistical values by aggregating all possible combinations of the items in the expression list and merging the grouping sets. 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 cannot be guaranteed. The number of grouping levels or total counts of the `n`th `CUBE` expression, which is the number of items in the expression list, namely, the number of groups, increases exponentially with the value of `n`. Therefore, we recommend that you specify a `CUBE` expression that contains no more than three 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 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 of the rows and thus the consistency of the results.
OFFSET	Specifies the number of rows to skip before the query result is returned. The `offset` parameter must be a numeric value or an expression that results in a numeric value. If a negative value is specified, the `offset` value is treated as 0. If `NULL` or a value greater than or equal to the number of rows returned by the query 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 paging starts from the first row.
ROW \| ROWS	Specifies a single row or multiple rows. You can choose the keyword that clearly conveys the semantics.
FETCH	Specifies the number or percentage of rows to return. If this parameter is not specified, all rows after the `offset` value are returned.
FIRST \| NEXT	Specifies the number or percentage of first returned rows or the number or percentage of next returned rows.
rowcount \| percent PERCENT	Use the `rowcount` parameter to specify the number of rows to return. The `rowcount` parameter must be a numeric value or an expression that results in a numeric value. If a negative value is specified, the `rowcount` 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 `rowcount` parameter 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 results in a numeric value. If a negative value is specified, the `percent` 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	Locks all rows in the query result set to prevent concurrent modifications by other transactions or concurrent reads by transactions with some isolation levels. `OF column`: In a multi-table join, you can use this clause to specify to lock only some tables (tables where the `column` specified in the clause resides). `WAIT`: After waiting for `Integer` time units, an attempt is made to lock the rows in the query result set. If any row is locked by another session, the attempt fails. `NOWAIT`: An attempt is immediately made to lock the rows in the query result set. 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 for locking rows in the result set of a multi-table join.
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` clause 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.

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 rows where the sum is less than 160, and output the results.

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 with 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 with 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 with 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 the data in 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 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 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 salary.

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 same salary as the last employee obtained in the previous example and the top 25% of employees based on the lowest salary.

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 RANDDOM() function to generate a random number and the FROM clause to directly query the GENERATOR() function.

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