SELECT

FROM table_reference_list [WHERE search_condition] [GROUP BY column_name [, column_name]... ] [HAVING search_condition] [hierarchical_condition]

The SELECT statement retrieves values from a database. You can select 0 or more records from one or more tables. A SELECT operation is typically called a query.

LEVEL is a pseudocolumn valid in the context of the hierarchical query only. If the result set is viewed as a tree of inter‑referenced rows, the LEVEL column produces the tree level number, assigning “1” to the top-level row.

See query_specification and Pseudo columns in SELECT statement.

See table_reference.

See search_condition.

If your table contains hierarchical data, you can select rows in a hierarchical order using a hierarchical query clause. In a hierarchical query clause, START WITH specifies the root row(s) of the hierarchy and CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. The CONNECT BY condition cannot contain a subquery.

In a hierarchical query, one expression in the condition must be qualified with the PRIOR operator to refer to the parent row. PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query. PRIOR is most commonly used when comparing column values with the equality operator. The PRIOR keyword can be on either side of the operator.

ORDER SIBLINGS BY causes the rows at any level to be ordered accordingly. See also expression.

The non-standard clause LIMIT row_count OFFSET skipped_rows allows to mask out a portion of a result set with a sliding window having the size of row_count and positioned at the skipped_rows+1 row. A negative value of skipped_rows results in an error, while the negative value of row_count results in the whole result set produced.

Two formats are available: for example LIMIT 24 OFFSET 10 is equal to LIMIT 10, 24.

The FETCH FIRST ROW ONLY clause returns only the first row of the table. FETCH FIRST constant ROWS ONLY returns the number of rows defined with constant.

When a user accesses a row with the SELECT... FOR UPDATE statement, the row is locked with an update mode lock. This means that no other user can read or update the row, and ensures the current user can later update the row. See Concurrency control and locking.

SELECT * FROM SYS_TABLES WHERE TABLE_NAME LIKE 'A%' LIMIT 5; SELECT * FROM SYS_TABLES WHERE TABLE_NAME LIKE 'A%' FETCH FIRST 5 ROWS ONLY; -- same as above

SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;

LAST_NAME	EMPLOYEE_ID	MANAGER_ID
King	100
Cambrault	148	100
Bates	172	148
Bloom	169	148
Fox	170	148
Kumar	173	148
Ozer	168	148
Smith	171	148
De Haan	102	100
Hunold	103	102
Austin	105	103
Ernst	104	103
Lorentz	107	103
Pataballa	106	103
Errazuriz	147	100
Ande	166	147
Banda	167	147

START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name ORDER BY LEVEL;

LAST_NAME	EMPLOYEE_ID	MANAGER_ID	LEVEL
King	100	NULL	1
Cambrault	148	100	2
De Haan	102	100	2
Bates	172	148	3
Bloom	169	148	3
Gates	104	148	3
Hunold	103	102	3
Hope	202	172	4
Smith	201	172	4