How a query is evaluated
The logical order of a SELECT, three-valued logic and types.
A SELECT is written in one order and evaluated in another. Knowing the logical order explains most error messages, and why some queries that look right are not.
| Step | Clause | What happens |
|---|---|---|
| 1 | FROM, JOIN | the rows of the tables, combined |
| 2 | WHERE | rows that fail the condition are dropped |
| 3 | GROUP BY | rows are gathered into groups |
| 4 | HAVING | groups that fail the condition are dropped |
| 5 | SELECT | the output columns are computed, and named |
| 6 | DISTINCT | duplicate output rows are removed |
| 7 | ORDER BY | the result is sorted |
| 8 | LIMIT, OFFSET | a page of the result is kept |
So a column alias made in SELECT cannot be used in WHERE (step 2 runs before step 5), but can be used in ORDER BY (step 7 runs after).
SELECT policy_number, ROUND(annual_premium / 12, 2) AS monthly
FROM policies
WHERE annual_premium / 12 > 150
ORDER BY monthly DESC
LIMIT 5;WHERE monthly > 150 would fail: monthly does not exist yet when WHERE runs.
Three-valued logic
A comparison with null is neither true nor false but unknown, and WHERE keeps only the rows for which the condition is true. So broker_id = NULL matches nothing, not even the rows where it is null: test with IS NULL and IS NOT NULL.
| a | b | a and b | a or b |
|---|---|---|---|
| true | unknown | unknown | true |
| false | unknown | false | unknown |
| unknown | unknown | unknown | unknown |
SELECT COUNT(*) AS direct,
COUNT(*) FILTER (WHERE broker_id = NULL) AS compared_with_equals
FROM policies
WHERE broker_id IS NULL;= NULL finds none of them.
NOT IN (list) returns no rows at all if the list contains a null. Prefer NOT EXISTS for 'rows without a match'.Types and casts
Every value has a type, and PostgreSQL converts between types only when it is safe. Convert explicitly with ::: '2025-12-31'::DATE, COUNT(*)::NUMERIC. Dividing two integers gives an integer, so 7 / 2 is 3; 7 / 2.0 or 7::NUMERIC / 2 is 3.5.
Functions
Built-in functions compute a value from each row: ROUND(x, 2), COALESCE(a, b) (the first value that is not null), UPPER(TEXT), AGE(DATE, DATE), EXTRACT(YEAR FROM DATE). Date arithmetic works directly: subtracting two dates gives the days between them.
Check your understanding
Answer every question, then check.