SIMPLE SELECT|V4.1.0| docs|Distributed Database

SIMPLE SELECT

Last Updated：2023-07-28 02:55:43 Updated

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

Purpose

You can use this statement 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 | 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	Optional. The hint option.
DISTINCT \| UNIQUE \| ALL	The returned data may contain duplicate rows. If you specify `DISTINCT` or `UNIQUE`, rows are deduplicated in the query results. `DISTINCT` and `UNIQUE` are synonyms. If you specify `ALL`, all rows are returned in the query results. The default value is `ALL`.
select_expr_list	The expression or names of columns to query from the database. Separate multiple names with commas (,). The value "" indicates all columns. `table_name.`: specifies all columns in the specified table or view. `table_alias_name.`: the alias of the table or view. `expr [[AS] column_alias_name]`: the alias of the column or expression that you want to query. `AS` is optional.
FROM table_references	The object from which data is selected.
PARTITION(partition_list)	The partition information of the table to be queried. Example: `partition(p0,p1…)`.
table_alias_name	The alias of the object from which data is selected, in the format of original name+space+alias.
joined_table	The join method in a multi-table query. `[INNER] JOIN` indicates inner join and `INNER` is optional. Only the data that meets the join conditions is returned. `[OUTER] JOIN` indicates outer join and `OUTER` is optional. `LEFT [OUTER]` indicates left (outer) join. The values of all public columns in the table on the left side are returned. `RIGHT [OUTER]` indicates right (outer) join. The values of all public columns in the table on the right side are returned. `FULL [OUTER]` indicates full (outer) join. The rows that are not returned in the inner join results of two tables are reserved and filled with null values.
ON expression	The join condition in a multi-table join.
WHERE where_conditions	Optional. The filter condition. Only data that meets the condition is returned in the query results. `where_conditions` is an expression.
GROUP BY group_by_list	The grouping field, which is usually used together with aggregate functions. Note: If no column following the `SELECT` clause uses any aggregate function, in a GROUP BY query, the columns following the `SELECT` clause must be specified in the `GROUP BY` clause.
ROLLUP group_expression_list	Rolls up the results of the groups created by the GROUP BY clause and generates the statistics.
GROUPING SETS group_expression_list	Specifies two or more data groups in a query, generates statistics, and aggregates and displays the statistics of the specified group. You can specify a single field or a list of fields in `GROUPING SETS`.
HAVING search_confitions	Filters the grouped data. The `HAVING` clause is similar to the `WHERE` clause, but the `HAVING` clause can reference an aggregate function such as `SUM` and `AVG`.
ORDER BY order_list	Sorts the query results by one or multiple columns in ascending (`ASC`) or descending (`DESC`) order. If you do not specify `ASC` or `DESC`, the default value `ASC` prevails. `ASC` indicates ascending order. * `DESC` indicates descending order.
row_limiting_clause	Restricts the rows returned by a query, to implement querying by page. You can specify the offset and the number of rows or percentage of rows to return. It can be used together with the `ORDER BY` clause to ensure the sorting order, to obtain consistent results.
OFFSET	The number of rows to skip before a query by page begins. `offset` must be a numeric value or an expression that evaluates to a numeric value. If you specify a negative number, `offset` is taken as 0. If you set it to `NULL` or a value equal to or greater than the number of rows returned by the query, 0 rows are returned. If `offset` contains a fraction, the fraction is truncated. If this clause is not specified, the offset is 0 and pagination starts from the first row.
ROW \| ROWS	Indicates a single row or multiple rows. You can select the keyword based on the number of rows to ensure clear semantics.
FETCH	The number of rows or percentage of rows to return. If this clause is not specified, all rows starting from the (`offset` + 1)th row are returned.
FIRST \| NEXT	Specifies the number or percentage of rows to return in the first or next response.
rowcount \| percent PERCENT	Use `rowcount` to specify the number of rows to return. `rowcount` must be a numeric value or an expression that evaluates to a numeric value. If you specify a negative number, `rowcount` is taken as 0. If `rowcount` is greater than the number of available rows starting from the (`rowcount` +1)th row, all available rows are returned. If `rowcount` contains a fraction, the fraction is truncated. If `rowcount` is set to `NULL`, 0 rows are returned. Use `percent PERCENT` to specify the percentage of rows to return. `percent` must be a numeric value or an expression that evaluates to a numeric value. If you specify a negative number, `percent` is taken as 0. If `percent` is set to `NULL`, 0 rows are returned. If you do not specify `rowcount` or percent``PERCENT, one row is returned.
ONLY \| WITH TIES	`ONLY` specifies the number of rows or the percentage of rows to return. `WITH TIES` specifies to return other rows that have the same sorting key as the last row obtained. If you use `WITH TIES`, you must specify the `ORDER BY` clause. If you do not specify `ORDER BY` clause, no other rows will be returned.
FOR UPDATE	Imposes an exclusive lock on all the rows in the query results to prevent other concurrent transactions from changing or reading the rows in some transaction isolation levels. `OF column`: instructs the database to lock the rows of a particular table only (the table where the column specified by the `column` keyword is located) in multi-table joins. `WAIT`: instructs the database to wait for a period specified by `Integer` before locking the rows in the query result set. If any row in the result set has been locked by another session, the query fails afterward. `NOWAIT`: locks rows in the query result set. If any row in the result set is locked by another session, the query fails afterward. `SKIP LOCKED`: skips the rows in the result set that are locked by another session and returns the unlocked rows. Note: `SKIP LOCKED` is not supported in a multi-table `join`.
pivot_clause	Pivots a row to a column.
aggregate_function	The aggregate function.
expr	The expression whose calculation results are constant values. `pivot_in_clause` supports only constant expressions.
unpivot_clause	Pivots a column to a row.
dblink_name	The name of the database link (dblink) to be accessed.

Examples

Query the data in the name field 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

Query and deduplicate the data in the name field from the tbl1 table.

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

Query the id, name, and num values from the tbl1 table, divide the num values by 2, and name the output column as 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 values of the id, name, and num fields 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 and num values from the tbl1 table, calculate the sum of the num values by name, and return the rows with a sum of 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 values of the id, name, and num fields from the tbl1 table, and return the query results by num 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, and return the query results by name in descending order and by num 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 row with a specified id from the tbl1 table, and use the FOR UPDATE clause to lock the row in the result set.

/*Query and lock the row where the ID is 1 in the tbl1 table of 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 where the ID is 1 or 2 in the tbl1 table of 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

Query rows from the tbl1 table by name and num and calculate the number of rows in each result set.

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 to columns, and then pivot the columns to 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 to 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

/*Pivot the columns in the emp table to 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 the data of a table in the remote database.

/*Access the remote OceanBase database to query data.*/
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

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

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

Examples of pagination queries.

Query the three employees with the smallest employee IDs (empno).

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 three employees with the smallest employee IDs (empno) again.

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

Query the 25% of employees with the lowest salaries.

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 25% of employees with the lowest salaries and all other employees with the same salary as the last employee returned 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