The Logical Order of a Query

SQL is written in one order and understood in another. You write SELECT first because humans like to see the requested output first, but the database has to find rows, filter them, group them, name the output, sort it, and only then return the final slice.

This lesson gives you the map: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT. It explains why aliases work in ORDER BY but not WHERE, why WHERE and HAVING are different, and how to debug a query by peeling clauses back.

The pipeline, in one report

Here is a complete Section 4 report: stocked categories with at least two items, sorted by their stocked total, capped at two rows.

SELECT category,
       COUNT(*) AS stocked_items,
       SUM(price) AS stocked_total
FROM menu_items
WHERE in_stock = true
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY stocked_total DESC
LIMIT 2;

 category | stocked_items | stocked_total
----------+---------------+---------------
 pastry   |             3 |         10.90
 coffee   |             3 |         10.30
(2 rows)

Read the query logically, not top to bottom:

• FROM menu_items: start with the table's rows.
• WHERE in_stock = true: keep only stocked item rows.
• GROUP BY category: fold those rows into category summaries.
• HAVING COUNT(*) >= 2: keep only groups with at least two stocked items.
• SELECT ...: build the output columns and aliases.
• DISTINCT: if present, remove duplicate output rows.
• ORDER BY stocked_total DESC: sort the final summary rows.
• LIMIT 2: return the first two rows from that sorted result.

The optimizer can choose clever physical steps under the hood. This order is the logical order: the dependency map that tells you which names and values exist at each point.

Alias scope: WHERE is too early

The alias rule from 2.4 is no longer a random PostgreSQL quirk. Watch it fail:

SELECT name,
       price * 0.9 AS sale_price
FROM menu_items
WHERE sale_price < 4.00
ORDER BY sale_price ASC;

ERROR:  column "sale_price" does not exist
LINE 4: WHERE sale_price < 4.00
              ^

sale_price is created in the SELECT step. WHERE runs earlier, while PostgreSQL is still deciding which raw rows survive. The fix is to use the real expression in WHERE, then use the alias in ORDER BY, where it exists:

SELECT name,
       price * 0.9 AS sale_price
FROM menu_items
WHERE price * 0.9 < 4.00
ORDER BY sale_price ASC, name ASC
LIMIT 4;

      name       | sale_price
-----------------+------------
 Sparkling Water |      1.980
 Espresso        |      2.250
 Earl Grey       |      2.700
 Croissant       |      2.880
(4 rows)

Same alias, different place in the pipeline. ORDER BY is late enough to see output names.

DISTINCT before ORDER BY and LIMIT

DISTINCT works on the selected output rows. Then ORDER BY sorts those unique rows. Then LIMIT slices the sorted answer.

SELECT DISTINCT category
FROM menu_items
WHERE in_stock = true
ORDER BY category
LIMIT 3;

 category
----------
 coffee
 drinks
 pastry
(3 rows)

If LIMIT happened before DISTINCT, this kind of query would mean "take any three stocked item rows, then deduplicate their categories." That is not the rule. The logical order is: filter stocked rows, select their category values, remove duplicates, sort the remaining categories, then take the first three.

This is also why 2.3 insisted on ORDER BY before LIMIT when you care which rows you get. Limit is a final slice, not a definition of "top" by itself.

Debug by peeling clauses back

When a query surprises you, do not stare harder at the finished version. Peel it back to the earliest stage that can be checked.

For the stocked-category report, the first question is: "Which raw rows enter the groups?"

SELECT category, name, price
FROM menu_items
WHERE in_stock = true
ORDER BY category, name;

 category |      name       | price
----------+-----------------+-------
 coffee   | Cappuccino      |  3.80
 coffee   | Espresso        |  2.50
 coffee   | Flat White      |  4.00
 drinks   | Sparkling Water |  2.20
 pastry   | Banana Bread    |  3.60
 pastry   | Cinnamon Roll   |  4.10
 pastry   | Croissant       |  3.20
 tea      | Earl Grey       |  3.00
(8 rows)

Now the grouped result makes sense: drinks and tea each have one stocked row, so HAVING COUNT(*) >= 2 removes them. Coffee and pastry each have three stocked rows, so they survive.

The debugging rhythm is simple:

• Start with FROM plus the row-level WHERE.
• Add GROUP BY and aggregates.
• Add HAVING.
• Add ORDER BY.
• Add LIMIT last.

Try that rhythm in the playground. The starter query is the finished report; the commented queries are useful checkpoints when the answer feels wrong.

You now have the whole single-table reading toolkit: columns, filters, sorting, aliases, types, NULL, CASE, aggregates, grouping, HAVING, and the pipeline that explains how they fit. Next is the Data Detective project, where you will use these pieces to answer real business questions against the cafe data.