Use the SELECT statement to retrieve values from a database. You can select 0 or more records from one or more tables. A SELECT operation is typically called a ‘query’.
Parameters, clauses, keywords, and variables
▪ LEVEL: A pseudocolumn that is 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.
▪ hierarchical‑condition: If your table contains hierarchical data, you can select rows in a hierarchical order.
– START WITH: Specifies the root row(s) of the hierarchy.
– CONNECT BY: Specifies the relationship between parent rows and child rows of the hierarchy. The CONNECT BY condition cannot contain a subquery.
– PRIOR: A unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the expression that follows it for the parent row of the current row in a hierarchical query.
In a hierarchical query, one expression in the condition must be qualified with the PRIOR operator to refer to the parent row.
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 BY: Causes the rows at any level to be ordered accordingly. See also, expression.
▪ row-limit-condition:
– LIMIT: The non-standard clause LIMIT row‑count OFFSET skipped‑rows allows you to mask out a portion of a result set with a sliding window that has the size of row‑count and is positioned at row number (skipped‑rows + 1). A negative value of skipped‑rows results in an error, while the negative value of row‑count results in the whole result set.
Two formats are available: for example LIMIT 24 OFFSET 10 is equal to LIMIT 10, 24.
– FETCH FIRST ROW ONLY: Returns only the first row of the table.
– FETCH FIRSTconstant ROWS ONLY: Returns the number of rows defined by constant.
▪ FOR UPDATE: 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 update the row later, see Concurrency control and locking.
Examples
SELECT ID FROM TEST;
SELECT DISTINCT ID, C FROM TEST WHERE ID = 5;
SELECT DISTINCT ID FROM TEST ORDER BY ID ASC;
SELECT NAME, ADDRESS FROM CUSTOMERS
UNION
SELECT NAME, DEP FROM PERSONNEL;
SELECT dept, count(*) FROM person
GROUP BY dept
ORDER BY dept
LIMIT 20 OFFSET 10
Example of LIMIT and FETCH
SELECT * FROM SYS_TABLES LIMIT 1;
SELECT * FROM SYS_TABLES FETCH FIRST ROW ONLY; -- same as above
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
Example of START WITH
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
Example of LEVEL and ORDER SIBLINGS BY
SELECT last_name, employee_id, manager_id, LEVEL FROM employees
START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name ORDER BY LEVEL;