Purpose
This statement is used to query data from one or more tables, views, or subqueries.
This section describes the general SELECT syntax in Oracle-compatible mode. For other SELECT syntaxes, see:
Privilege requirements
To execute a SELECT statement, the current user must have the SELECT privilege on the target table or view. For more information about OceanBase Database privileges, see Privilege types in Oracle-compatible mode.
Syntax
select_stmt:
subquery [order_by_clause] [fetch_next_clause] [for_update_clause]
subquery:
select_no_parens
| select_with_parens
| with_select
fetch_next_clause:
[OFFSET offset_expr {ROW | ROWS}]
FETCH {FIRST | NEXT} [fetch_expr] {ROW | ROWS} {ONLY | WITH TIES}
| [OFFSET offset_expr {ROW | ROWS}]
FETCH {FIRST | NEXT} fetch_percent_expr PERCENT {ONLY | WITH TIES}
for_update_clause:
FOR UPDATE [OF column_list] [for_update_wait]
for_update_wait:
WAIT {decimal | intnum}
| NOWAIT
| SKIP LOCKED
Parameters
Parameter |
Description |
|---|---|
| subquery | The query body can be a simple SELECT statement, a subquery with parentheses, orWITHSubquery. For more information, see SIMPLE SELECT and the WITH clause. |
| order_by_clause | Optional. Sorts the result set. Syntax: ORDER BY expr [ASC \ | DESC] [NULLS FIRST \ | NULLS LAST]. In Oracle-compatible mode, the default isNULLS LAST(ascending order) orNULLS FIRST` (descending order). |
| fetch_next_clause | Optional. Specifies the number of rows to return. In Oracle-compatible mode, useFETCH FIRST/NEXTReplaces the MySQL-compatible modeLIMIT. |
| OFFSET offset_expr | Optional. Specifies the number of rows to skip. The default value is 0. |
| FETCH FIRST/NEXT | Specify the number of rows or percentage to return.FIRSTandNEXTEquivalent.WITH TIESRequires cooperation withORDER BY, which returns all rows that have the same sorted value as the last row. |
| FOR UPDATE | Optional. Acquires an exclusive lock on the query results to prevent other transactions from modifying these rows. |
| OF column_list | Optional. Specifies the column to lock. |
| WAIT n | Optional. The timeout period (in seconds) for waiting for the lock to be released. |
| NOWAIT | Optional. If the lock cannot be acquired immediately, an error is returned without waiting. |
| SKIP LOCKED | Optional. Specifies to skip rows locked by other transactions. |
Features unique to Oracle-compatible mode
DUAL TABLES
DUAL is a virtual table in Oracle-compatible mode, used to perform calculations or call functions when no actual table is available.
ROWNUM Pseudo-Column
ROWNUM is a pseudo-column in Oracle-compatible mode that assigns an incremental serial number (starting from 1) to each row in the query result set. It is commonly used to limit the number of rows returned.
Hierarchical query
Oracle-compatible mode supports the START WITH ... CONNECT BY syntax for hierarchical (tree-based) queries. For more information, see SIMPLE SELECT.
Set operations
Oracle-compatible mode supports the set operations UNION, UNION ALL, INTERSECT, and MINUS. For more information, see Set operations in SELECT statements.
Examples
The tables and data in the examples are defined as follows:
Create a test table:
CREATE TABLE employees (employee_id INT PRIMARY KEY, employee_name VARCHAR2(20), salary NUMBER, department_id INT, manager_id INT);Insert test data:
INSERT INTO employees VALUES (1, 'Alice', 8000, 10, NULL); INSERT INTO employees VALUES (2, 'Bob', 12000, 10, 1); INSERT INTO employees VALUES (3, 'Charlie', 9500, 20, 1); INSERT INTO employees VALUES (4, 'David', 15000, 20, 1); INSERT INTO employees VALUES (5, 'Eve', 11000, 10, 1);Submit data:
COMMIT;
Basic queries
Query all columns of data from the employees table.
obclient> SELECT * FROM employees;
The return result is as follows:
+-------------+---------------+--------+---------------+------------+
| EMPLOYEE_ID | EMPLOYEE_NAME | SALARY | DEPARTMENT_ID | MANAGER_ID |
+-------------+---------------+--------+---------------+------------+
| 1 | Alice | 8000 | 10 | NULL |
| 2 | Bob | 12000 | 10 | 1 |
| 3 | Charlie | 9500 | 20 | 1 |
| 4 | David | 15000 | 20 | 1 |
| 5 | Eve | 11000 | 10 | 1 |
+-------------+---------------+--------+---------------+------------+
5 rows in set
Use the DUAL table
Use the DUAL table to perform calculations and function calls.
obclient> SELECT 1 + 1, SYSDATE FROM DUAL;
Limit the number of rows returned by using ROWNUM
Query the first 3 rows of data from the employees table.
obclient> SELECT employee_name, salary FROM employees WHERE ROWNUM <= 3;
The return result is as follows:
+---------------+--------+
| EMPLOYEE_NAME | SALARY |
+---------------+--------+
| Alice | 8000 |
| Bob | 12000 |
| Charlie | 9500 |
+---------------+--------+
3 rows in set
Limit the number of rows returned by using FETCH FIRST
Query the employees table for the top 3 employees by salary.
obclient> SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;
The return result is as follows:
+---------------+--------+
| EMPLOYEE_NAME | SALARY |
+---------------+--------+
| David | 15000 |
| Bob | 12000 |
| Eve | 11000 |
+---------------+--------+
3 rows in set
Implement pagination using OFFSET and FETCH
Query the third to fourth rows of data in the employees table after sorting by name.
obclient> SELECT employee_name, salary
FROM employees
ORDER BY employee_name
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;
The return result is as follows:
+---------------+--------+
| EMPLOYEE_NAME | SALARY |
+---------------+--------+
| Charlie | 9500 |
| David | 15000 |
+---------------+--------+
2 rows in set
Lock query results with FOR UPDATE
Query employees in department 10 and acquire an exclusive lock.
obclient> SELECT employee_name, salary FROM employees WHERE department_id = 10 FOR UPDATE;
The return result is as follows:
+---------------+--------+
| EMPLOYEE_NAME | SALARY |
+---------------+--------+
| Alice | 8000 |
| Bob | 12000 |
| Eve | 11000 |
+---------------+--------+
3 rows in set
Hierarchical query
Use the START WITH ... CONNECT BY clause to query the organizational hierarchy.
obclient> SELECT LEVEL, employee_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
The return result is as follows:
+-------+---------------+------------+
| LEVEL | EMPLOYEE_NAME | MANAGER_ID |
+-------+---------------+------------+
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 2 | Charlie | 1 |
| 2 | David | 1 |
| 2 | Eve | 1 |
+-------+---------------+------------+
5 rows in set