Restrict and Sort Data
What the domain covers
This domain tests restricting rows with WHERE and ordering with ORDER BY. It covers comparison operators, logical operators, BETWEEN, IN, LIKE, NULL tests, and null ordering.
Core rules
- WHERE filters rows before grouping and accepts conditions using =, !=, <>, >, >=, <, <=.
- AND has higher precedence than OR; use parentheses to force intended logic.
- BETWEEN x AND y is inclusive.
- IN is a set membership test. NOT IN with a NULL in the list returns no rows because comparison to NULL is unknown.
- LIKE uses % and _; ESCAPE defines an escape character.
- NULL cannot be tested with =; use IS NULL or IS NOT NULL.
- ORDER BY sorts ascending by default. In Oracle, ascending places NULLs last; descending places NULLs first. Use NULLS FIRST or NULLS LAST to override.
- ORDER BY can use column aliases and expressions; WHERE cannot use SELECT aliases.
Worked example
SELECT last_name, salary FROM employees WHERE hire_date BETWEEN DATE '2005-01-01' AND DATE '2005-12-31' ORDER BY salary DESC NULLS LAST;
Reasoning: BETWEEN includes both boundary dates. DATE literals avoid reliance on session date format. ORDER BY sorts highest salary first; NULLS LAST keeps employees with no salary at the end despite DESC. Without NULLS LAST, descending would put NULLs first.
Traps
NOT IN with a NULL can silently return zero rows. LIKE '_%' requires at least one character, unlike %. Default null ordering depends on ASC or DESC. Do not put ORDER BY before WHERE; clause order is fixed.