# SQL’s logical query order

> FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY — why an alias works in one clause and not another.

Concept · Writing queries · https://levelcode.org/concepts/sql-logical-order

You write a query starting with `SELECT`, but the database doesn't read it in that order. Each clause works on the output of the one before it, in a fixed **logical order**:

```text
1. FROM / JOIN    build the rows: tables, joined
2. WHERE          keep rows that match
3. GROUP BY       collapse rows into groups
4. HAVING         keep groups that match
5. SELECT         compute output columns (and aliases)
6. DISTINCT       remove duplicate output rows
7. ORDER BY       sort
8. LIMIT/OFFSET   cut
```

This is the order the *result* is defined in. The planner is free to execute it differently, for example by using an index so it never reads the rows `WHERE` would throw away. But it must return what this order would produce.

## What the order explains

Most "why is this an error?" moments in SQL come from this list.

- **An alias from `SELECT` isn't visible in `WHERE`.** `WHERE` runs at step 2, and the alias doesn't exist until step 5. Repeat the expression, or compute it in a subquery or CTE.
- **`ORDER BY` can use the alias.** It runs after `SELECT`, so `ORDER BY total DESC` works.
- **You can't use an aggregate in `WHERE`.** No groups exist yet at step 2. To filter on `count(*)`, use `HAVING`.
- **Every non-aggregated column in `SELECT` must be grouped.** After step 3 there's one row per group, so a column that differs within a group has no single value.
- **Window functions can't be filtered in `WHERE`.** They're computed alongside `SELECT`. Wrap the query and filter outside.

```sql
-- Customers with more than 5 orders, biggest spenders first
SELECT customer_id, count(*) AS orders, sum(total) AS spent
FROM orders
WHERE status = 'paid'          -- row filter: step 2
GROUP BY customer_id
HAVING count(*) > 5            -- group filter: step 4 (alias not usable yet)
ORDER BY spent DESC;           -- alias usable: step 7
```

## WHERE or HAVING?

Put a condition in `WHERE` if it's about individual rows, and in `HAVING` only if it's about the group. Filtering early means fewer rows to group, so it's usually faster. It's also clearer about what you meant.

When a filter on an outer join behaves strangely, the cause is usually the order plus [NULL semantics](https://levelcode.org/concepts/null-three-valued-logic). A `WHERE` on the right-hand table runs after the join, so it throws away the NULL-padded rows the `LEFT JOIN` kept. Put that condition in the `ON` clause instead.