Skip to content

Basics & filtering · Easy

In what order does SQL logically evaluate the clauses of a SELECT query?

Short answer

Logically, SQL runs FROM and JOINs first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. That is why a column alias defined in SELECT cannot be used in WHERE, but can usually be used in ORDER BY. The engine may physically reorder work, but results must match this logical order.

Detailed answer

The written order (SELECT … FROM … WHERE …) is not the order in which the clauses are evaluated. The logical order is:

  1. FROM / JOIN – build the working set of rows.
  2. WHERE – filter individual rows.
  3. GROUP BY – collapse rows into groups.
  4. HAVING – filter groups.
  5. SELECT – compute output columns and aliases (window functions run here).
  6. DISTINCT – remove duplicate output rows.
  7. ORDER BY – sort.
  8. LIMIT / OFFSET – trim.

Why interviewers ask: it explains a whole family of errors. You cannot filter on an alias in WHERE because the alias does not exist yet, and you cannot put a window function in WHERE because windows are computed after filtering.

-- Works: the alias is used in ORDER BY, which runs after SELECT
SELECT product_name,
       price - cost AS unit_margin
FROM products
WHERE price - cost > 1000      -- repeat the expression; the alias is not visible here
ORDER BY unit_margin DESC;

Pitfall: SQLite and MySQL are lenient and sometimes allow aliases in WHERE or HAVING. Do not rely on that in an interview; write portable SQL or wrap the logic in a CTE.