Retrieving and Restricting Data with SELECT
Retrieving and Restricting Data with SELECT
This domain tests whether you can project the columns you need, filter rows with predicates, and control row order. Oracle evaluates a query logically as FROM, WHERE, SELECT, then ORDER BY, which explains what each clause can reference.
Core rules
- SELECT * returns every column; explicit column lists and aliases improve clarity. An alias is available in ORDER BY but not in WHERE.
- Use =, <>, >, >=, <, <= for ordinary comparisons; test missing values only with IS NULL or IS NOT NULL.
- BETWEEN is inclusive. LIKE uses % and _; add ESCAPE when the data contains wildcard characters.
- AND binds tighter than OR; add parentheses rather than relying on precedence.
- DISTINCT applies to the complete select list, not to one column.
- ORDER BY supports ASC/DESC and NULLS FIRST/LAST. For ascending order Oracle places nulls last by default; descending order places them first.
Worked example
SELECT employee_id, last_name, salary FROM employees WHERE department_id IN (10, 20) AND salary BETWEEN 5000 AND 10000 ORDER BY salary DESC, last_name;
Rows must satisfy both predicates. ORDER BY sorts by salary from highest to lowest, then breaks ties alphabetically by last_name. If salary is null, that row sorts first in this descending order unless NULLS LAST is specified.
Traps
Writing WHERE salary = NULL returns no rows. Putting a select-list alias in WHERE fails. NOT IN becomes unsafe when the list contains NULL. A missing join condition in FROM creates a Cartesian product. DISTINCT does not remove duplicates based on one column when several columns are selected. ORDER BY 2 sorts by the second expression, not by a literal 2.