Module 1 · Foundations

How a query really executes

Beginner 18 min read ⭐ Most important lesson

SQL is the only major language whose clauses do not run in the order you write them. Learn the real order once and a long list of confusing errors — aliases rejected in WHERE, HAVING vs WHERE, DISTINCT behaving oddly — collapses into a single rule.

After this lesson you can…

  • Recite the logical order of the seven clauses.
  • Explain exactly why WHERE total > 100 fails but ORDER BY total works.
  • Choose WHERE vs HAVING without guessing.
  • Say why the logical order is not the physical order, and why that is fine.

1. The seven steps

🏭

The assembly line

Think of a factory line where each station hands a pile of rows to the next. FROM loads the raw material. WHERE throws away rows nobody wants. GROUP BY crushes the survivors into batches. HAVING throws away whole batches. SELECT finally decides what labels to print. ORDER BY arranges the finished boxes, and LIMIT ships the first few.

You cannot ask station 2 to read a label that station 5 prints. That is the entire lesson.

1 · FROM + JOIN Build the working set: read tables, apply join conditions. 50,000 rows 2 · WHERE Discard individual ROWS. Cannot see aggregates or SELECT aliases. 8,400 rows 3 · GROUP BY Collapse rows into one row per group. Detail is GONE from here on. 12 groups 4 · HAVING Discard whole GROUPS. This is where aggregate conditions belong. 5 groups 5 · SELECT ← aliases are created HERE Evaluate output expressions and name them. Then DISTINCT, if asked. 5 rows 6 · ORDER BY Sort. CAN use SELECT aliases, because step 5 already ran. 7 · LIMIT / OFFSET — take the slice The rule in one line A clause can only see what EARLIER steps produced. WHERE is step 2, so it cannot use a step-5 alias. ORDER BY is step 6, so it can. HAVING is step 4, after grouping — so aggregates work.
Rows shrink as they travel down. Notice that most of the discarding happens early — which is also why filtering early is the cheapest optimisation you can make.

2. The alias question, settled

-- ❌ ERROR: no such column: total
SELECT   city, SUM(amount) AS total
FROM     orders
WHERE    total > 1000            -- WHERE is step 2; "total" is born at step 5
GROUP BY city;

-- ✅ works: ORDER BY is step 6, after SELECT
SELECT   city, SUM(amount) AS total
FROM     orders
GROUP BY city
ORDER BY total DESC;             -- the alias exists by now

Which clauses can see a SELECT alias?

ClauseStepSees SELECT aliases?
FROM / JOIN … ON1❌ No
WHERE2❌ No
GROUP BY3❌ standard; ✅ MySQL & Postgres allow it as an extension
HAVING4❌ standard; ✅ MySQL allows it
SELECT5❌ not its own aliases — you cannot reuse one in the same SELECT
ORDER BY6✅ Yes — everywhere

Three ways to filter on a computed value

-- 1. Repeat the expression (works everywhere, a bit ugly)
SELECT   city, SUM(amount) AS total
FROM     orders
GROUP BY city
HAVING   SUM(amount) > 1000;

-- 2. Wrap it in a subquery / derived table
SELECT *
FROM  (SELECT city, SUM(amount) AS total
       FROM orders GROUP BY city) t
WHERE t.total > 1000;

-- 3. Use a CTE - usually the most readable (lesson 13)
WITH by_city AS (
    SELECT city, SUM(amount) AS total
    FROM   orders
    GROUP  BY city
)
SELECT * FROM by_city WHERE total > 1000;

3. WHERE vs HAVING — never guess again

raw rows Kochi 500 PAID Kochi 300 VOID Pune 700 PAID Pune 100 PAID Delhi 200 PAID WHERE status='PAID' drops ROWS surviving rows Kochi 500 Pune 700 Pune 100 Delhi 200 GROUP BY groups Kochi 500 Pune 800 Delhi 200 HAVING SUM > 400 drops GROUPS final result Kochi 500 Pune 800 How to choose, every time Does the condition mention an AGGREGATE (SUM, COUNT, AVG…)? → HAVING. Otherwise → WHERE. And prefer WHERE when both would work: filtering 50,000 rows down to 8,400 BEFORE grouping is far cheaper than grouping all 50,000.
They are not alternatives — they sit on opposite sides of GROUP BY. Putting a row condition in HAVING usually still gives the right answer, but makes the database do far more work.

4. Why "column must appear in GROUP BY"

SELECT   city, full_name, SUM(amount)
FROM     orders JOIN customers USING (customer_id)
GROUP BY city;
-- ERROR: column "customers.full_name" must appear in the GROUP BY clause
--        or be used in an aggregate function

After step 3, one row represents all the Kochi orders — placed by hundreds of different people. Asking for full_name is asking "which one of those hundreds?", and the database refuses to pick arbitrarily. Your options: 

-- a) it belongs in the grouping
SELECT city, full_name, SUM(amount) FROM ... GROUP BY city, full_name;

-- b) aggregate it, saying explicitly which value you want
SELECT city, MAX(full_name) AS a_name, SUM(amount) FROM ... GROUP BY city;

-- c) count instead of naming
SELECT city, COUNT(DISTINCT customer_id) AS n_customers, SUM(amount) FROM ... GROUP BY city;

-- d) you did not want to group at all — use a WINDOW function (lesson 14)
SELECT city, full_name, amount,
       SUM(amount) OVER (PARTITION BY city) AS city_total
FROM   ...;                                   -- every row kept, total added
MySQL's dangerous historical default

Old MySQL allowed the invalid query and returned an arbitrary full_name — silently wrong data, not an error. MySQL 5.7+ enables ONLY_FULL_GROUP_BY by default and now rejects it like everyone else. If you meet a legacy MySQL that accepts it, treat the results with suspicion.

The functional-dependency exception

Grouping by a primary key lets you select any other column from that table, because the PK determines them all — one customer_id can only have one full_name. PostgreSQL and MySQL 8 both understand this: SELECT c.customer_id, c.full_name, SUM(o.amount) … GROUP BY c.customer_id is legal.

5. Logical order ≠ physical order

Everything above describes the meaning of your query. The optimiser is free to execute it completely differently, as long as the answer is identical. That freedom is the whole point of a declarative language.

Logical model saysThe engine may actually
Join everything, then filterPush the filter into an index seek so the join never sees those rows
Join A then B then C in written orderReorder joins by estimated cost
Group, then sort for ORDER BYReuse an index that is already in sorted order and skip the sort
Compute SELECT for every groupStop early when it sees LIMIT 10
Read the whole tableRead only the index if every needed column is in it (lesson 26)
 
EXPLAIN SELECT city, SUM(amount) FROM orders WHERE status = 'PAID' GROUP BY city;
-- Postgres: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
-- MySQL:    EXPLAIN ANALYZE SELECT ...
-- SQLite:   EXPLAIN QUERY PLAN SELECT ...
Why you still need the logical order

The optimiser guarantees the result, not your intuition. When you need to predict what a query means — can this clause see that alias, will this filter run before or after grouping — you reason with the logical order. When you need to know why it is slow, you read the physical plan (lesson 27). Two different questions, two different tools.

6. A worked example, step by step

SELECT   c.city,
         COUNT(*)        AS n_orders,
         SUM(o.amount)   AS revenue
FROM     orders o
JOIN     customers c ON c.customer_id = o.customer_id
WHERE    o.order_date >= '2024-01-01'
  AND    o.status = 'PAID'
GROUP BY c.city
HAVING   COUNT(*) >= 10
ORDER BY revenue DESC
LIMIT    5;
  1. FROM/JOIN — every paid-or-not order matched to its customer. Say 50,000 rows.
  2. WHERE — keep 2024 and PAID only. Now 8,400 rows. Aggregates are not available yet.
  3. GROUP BY c.city — 8,400 rows become 12 city groups.
  4. HAVING COUNT(*) >= 10 — drop cities with fewer than 10 orders. 5 groups remain.
  5. SELECT — compute city, n_orders, revenue. The names n_orders and revenue now exist.
  6. ORDER BY revenue DESC — legal, because step 5 created revenue.
  7. LIMIT 5 — take the top five.
One consequence worth internalising

LIMIT runs last. SELECT … LIMIT 10 over a billion rows with an ORDER BY still has to determine the global top 10 — it cannot just read ten rows and stop. (A good optimiser will use a top-N heap rather than a full sort, but it must still consider every row.) This is why "add LIMIT to make it fast" often does nothing.

Recap

  • FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
  • A clause sees only what earlier steps produced — hence no aliases in WHERE, but yes in ORDER BY.
  • WHERE filters rows, HAVING filters groups. Aggregate in the condition → HAVING.
  • Prefer WHERE when both work: less data reaches the grouping step.
  • "Must appear in GROUP BY" means you asked for a detail that no longer exists — group it, aggregate it, or use a window function.
  • The logical order is the meaning, not the execution. The optimiser may do anything that yields the same answer.

Checkpoint

1 · Why does WHERE revenue > 100 fail when revenue is a SELECT alias, while ORDER BY revenue works?
This is the clearest payoff of knowing the logical order. Names introduced by SELECT come into existence at step 5, so only steps 6 and 7 can refer to them. To filter on a computed value, repeat the expression, use HAVING if it is an aggregate, or wrap the query in a CTE or derived table.
2 · You want only orders with status = 'PAID', and only cities totalling over 10,000. Where does each condition go?
status is a property of an individual row, so it belongs in WHERE, where it also reduces the data before grouping. SUM(amount) only exists once rows have been grouped, so it must go in HAVING. Putting the status check in HAVING would still be correct but would force the engine to group rows it is about to discard.
3 · SELECT city, full_name, SUM(amount) FROM … GROUP BY city is rejected. Which fix keeps every order row visible and shows each city's total?
Adding to GROUP BY or aggregating both collapse rows, so you lose the per-order detail. A window function computes the city total alongside every original row without grouping anything away — exactly what "keep the rows and add context" means. That is lesson 14, and this error is the most common signal that a window function is what you actually wanted.
4 · A colleague adds LIMIT 10 to a slow ORDER BY revenue DESC query over a billion rows and is surprised it is still slow. Why?
To return the global top 10 by revenue, every candidate row must be examined. A good optimiser keeps a 10-element heap instead of sorting everything, which helps a lot, but the scan still happens. Real speedups come from reducing what must be scanned — a filter, a suitable index, or a pre-aggregated table.