SIMPLE SELECT |V3.2.4|OceanBase Database| docs|Distributed Database

SIMPLE SELECT

Last Updated：2023-10-24 09:23:03 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 } ] ]

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]

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	The hint option, which is optional.
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 expressions or column names that you want to query from the database. Separate them with commas (,). You can use an asterisk () to indicate all columns. `table_name.`: indicates selecting all columns from 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. For 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 nulls.
ON expression	The join condition in a multi-table join.
WHERE where_conditions	The filter condition, which is optional. Only the 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, the columns following the SELECT clause must be specified after 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_conditions	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 an ascending order. `DESC` indicates a descending order.
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 of time 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

Create a table named tbl1.

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

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

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 (0.00 sec)

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 (0.01 sec)

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 (0.03 sec)

/*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;