SELECT, FROM, WHERE, ORDER BY, LIMIT

Logical evaluation order

You write SELECT columns FROM table WHERE … ORDER BY … LIMIT n. The engine evaluates it as: take the rows FROM the table, keep those where the WHERE predicate is true, GROUP them if asked, filter groups with HAVING, compute the SELECT list, remove duplicates for DISTINCT, ORDER the result, and LIMIT it.

This order explains the rules people memorise. You cannot use a SELECT alias in WHERE because WHERE runs first. You can use it in ORDER BY because ORDER BY runs last. WHERE count(*) > 1 is an error because aggregates do not exist until GROUP BY. LIMIT without ORDER BY returns an arbitrary ten rows because the order was never defined.

Written order vs evaluation order

1SELECT   u.country, count(*) AS n      -- 5. project, compute aggregates
2FROM     users u                        -- 1. source rows
3WHERE    u.created_at >= '2025-06-01'   -- 2. filter rows (no aliases, no aggregates)
4GROUP BY u.country                      -- 3. collapse to groups
5HAVING   count(*) >= 10                 -- 4. filter groups
6ORDER BY n DESC                         -- 6. sort (aliases allowed)
7LIMIT    5;                             -- 7. cut

NULL is not a value

NULL means "unknown" and comparisons with it are neither true nor false — they are NULL. WHERE email = NULL matches nothing; WHERE email <> 'x' silently drops every row where email is NULL, because NULL <> 'x' is NULL, and WHERE keeps only true. Use IS NULL and IS NOT NULL. count(column) skips NULLs; count(*) does not. sum of no rows is NULL, not zero — wrap it in coalesce(sum(x), 0).

The most expensive NULL bug is NOT IN (subquery): if the subquery returns a single NULL, the whole predicate is NULL for every row and the query returns nothing, with no error. Prefer NOT EXISTS, which is NULL-safe. See Subqueries, CTEs, EXISTS, UNION, CASE.

x = NULL → NULL. x IS NULL → true/false.
NULL AND false → false; NULL OR true → true; everything else with NULL → NULL.
ORDER BY puts NULLs last ascending and first descending in PostgreSQL; say NULLS FIRST/LAST when it matters.
A UNIQUE constraint allows many NULLs — they are all "unknown", so none equals another.

Filtering: the predicates an index can use

=, <, >, BETWEEN, IN (list) and LIKE 'prefix%' can be answered by a B-tree index on the column. LIKE '%suffix', lower(col) = …, col + 1 = … and col::text = … cannot, because the index stores the raw column value and the predicate asks about something else. This is the sargable distinction, and it is the difference between an index scan and a full-table scan on the same table.

AND narrows and OR widens; NOT flips. OR across two different columns usually defeats a single index — the planner may combine two indexes with a bitmap, or give up and scan. Rewriting an OR as a UNION of two indexed queries is a standard optimisation.

Sargable vs not

1-- index on created_at can be used:
2WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
3 
4-- same intent, index cannot be used (column wrapped in a function):
5WHERE date_trunc('month', created_at) = '2026-01-01'
6 
7-- prefix match: usable.  suffix match: not.
8WHERE email LIKE 'jonas%'      -- range scan on the index
9WHERE email LIKE '%@example.com'  -- every row

DISTINCT, aliases and LIMIT/OFFSET

DISTINCT removes duplicate rows from the final projection and costs a sort or a hash of the whole result. It is frequently a band-aid over a join that produced duplicates; fix the join instead. DISTINCT ON (col) in PostgreSQL keeps the first row per value of col — a cheap top-1-per-group.

Aliases (AS) name columns and tables. Table aliases are not cosmetic: they are the only way to join a table to itself, and they make every column reference unambiguous. Column aliases are what you sort by and what the client sees.

LIMIT n OFFSET m is pagination that costs O(m): the engine produces and discards m rows first. Page 500 is 500× slower than page 1. Keyset pagination — WHERE id > :last ORDER BY id LIMIT n — is O(1) per page and is what every high-traffic list endpoint eventually uses.

Key points

Evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Aliases exist only after SELECT.
NULL comparisons yield NULL; WHERE keeps only true. Use IS NULL, coalesce, and NOT EXISTS over NOT IN.
A predicate is sargable when it compares the bare column to a value; wrapping the column defeats the index.
LIMIT without ORDER BY is an arbitrary subset. OFFSET is linear; keyset pagination is constant.

SQL playground

SQL Playground7 tables · 17,994 rows

View query internals →

SELECT id, name, country, created_at
FROM users
WHERE email = 'jonas.olsen7@example.com';

Run a query, or pick one from the list. Prefix it with EXPLAIN ANALYZE to see the plan the engine actually executed.

E-Commerce: 900 users, 240 products, 3,200 orders, 8,000 order items, payments and reviews. The default: it has a junction table, a self-referencing tree, deliberate denormalization and deliberately missing indexes.

Try it in the playground

SELECT id, name, country
FROM users
WHERE country = 'DE' AND last_login_at IS NOT NULL
ORDER BY created_at DESC
LIMIT 10;

Filter, sort, page. →

SELECT DISTINCT status FROM orders ORDER BY status;

Distinct values of a low-cardinality column. →

When to use — and when not

Use it when

Any read of a relational table.
Ad-hoc analysis — SQL is the fastest way to ask a question of data.

Avoid it when

Row-by-row procedural logic; SQL is a set language and loops belong in application code or a set-based rewrite.

Failure modes

NOT IN with a nullable subquery returning zero rows.
A filter on a function of a column that silently disables the index.
Pagination by OFFSET on a large table.

See how this works internally →

Descend one layer: the same topic explained from the machinery up.

How is this SQL parsed into something the engine can run?

↓ Database Internals → Tokens, Parse Tree, AST

What happens between SELECT and the result?

↓ Database Internals → Follow the Query