Intro

SQL separates meaning from execution. The logical query describes which rows belong in the result; parsing, binding, optimization, and execution turn that meaning into physical scans, seeks, joins, and aggregates. Read logical semantics to prove correctness and the execution plan to diagnose cost.

Logical Clause Order

The common logical order is FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/TOP. The engine can push predicates or reorder joins physically, but only when duplicates, NULL, and the final result remain equivalent.

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE hire_date >= DATE '2024-01-01'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC;

WHERE cannot see headcount because projection happens later. ORDER BY generally can. Do not generalize that rule to GROUP BY: PostgreSQL permits a simple output alias there, while SQL Server requires the original expression. HAVING rules also differ, so portable SQL repeats the grouped or aggregate expression.

Physical Pipeline

graph LR
    P1["Parse syntax to tree"] --> P2["Bind names and types"] --> P3["Optimize candidate plans"] --> P4["Execute physical operators"] --> P5["Read pages and indexes"]

Cardinality estimates connect the optimizer to storage. If a predicate is estimated at 10 rows but returns 1,000,000, a nested loop or join order that looked cheap can spill or repeat millions of probes. The SQL can remain correct while the plan is poor.

Join Semantics

Assume customers contains Ada and Lin, while orders contains two rows for Ada and none for Lin. A left join returns Ada twice and extends Lin’s missing order columns with NULL; joins do not deduplicate.

SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.total;

name | total
Ada  | 40
Ada  | 70
Lin  | NULL

Putting o.total >= 50 in ON preserves Lin as an unmatched left row. Putting it in WHERE removes Lin because NULL >= 50 is unknown.

Physical Join Choice

Operator	Strong fit	Cost to watch
Nested loop	Small outer input with indexed inner probes	Repeated inner work when estimates are wrong
Hash join	Large equality joins with enough memory	Build memory and spills
Merge join	Inputs already ordered on the join key	Sorting when order is absent

No operator is universally fastest. Row counts, ordering, widths, indexes, memory, and cache state determine the plan.

References

Query processing architecture guide — SQL Server’s parse, bind, optimize, and execute stages.
PostgreSQL query path — primary overview of parsing, planning, and execution.
PostgreSQL table expressions — primary reference for inner and outer join semantics and filtering order.
PostgreSQL SELECT — documents output-name visibility in ORDER BY and GROUP BY.
SQL Server SELECT: GROUP BY — documents SQL Server’s restriction on aliases defined in the same select list.
Using EXPLAIN — shows how estimates and costs drive physical scan and join choices.

Topics

DEVBOOK

SQL Query Processing and Joins