Explain the order in which SQL actually evaluates a query, and why it differs from the order you write it in.
The order you write is not the order it runs. The logical evaluation order is FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT. The optimizer may reorder the physical work as long as the result is identical, but it must respect this logical order, and that is what determines what you are allowed to write.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) NOT NULL
);
INSERT INTO orders (id, customer_id, amount, status) VALUES
(1, 1, 120.00, 'shipped'),
(2, 1, 340.50, 'pending'),
(3, 2, 75.25, 'shipped'),
(4, 2, 15.00, 'cancelled');
SELECT customer_id,
SUM(amount) AS total
FROM orders
WHERE status = 'shipped'
GROUP BY customer_id
HAVING SUM(amount) > 100
ORDER BY total DESC
LIMIT 5;The result is one row: customer 1 with a total of 120.00. The WHERE clause removed the pending and cancelled orders before anything was grouped, so customer 2's group contains only the 75.25 order, and the HAVING clause then discarded that group because 75.25 is not above 100.
Each stage explains a class of otherwise baffling errors. WHERE runs before the projection, so it cannot see a SELECT alias. GROUP BY runs before SELECT, so it cannot see a SELECT alias either in strict engines, though most accept one. HAVING runs after GROUP BY but before SELECT, and most engines resolve aliases there by looking ahead. ORDER BY runs after the projection, so it can use an alias. LIMIT runs last, after the sort, which is why ORDER BY is required for stable pagination.
Two more consequences. Aggregates are only meaningful once GROUP BY has run, so a WHERE clause can never contain SUM. And window functions are evaluated after GROUP BY, so they cannot appear in WHERE or HAVING at all — the top-row-per-group pattern needs a CTE or a derived table to give the window function a stage of its own.