What is the logical order of execution of a SQL query?

Q: What is the logical order of execution of a SQL query?

**Target Audience:** Beginners who can write a simple SELECT and want to understand why some queries fail with "column does not exist". **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15. MySQL and SQL Server differences are noted where they matter. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running the query; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Think of a cooking recipe card. The title at the top says "Chocolate cake, cut into 8 slices, served on a plate". That is the first thing you read, but it is the last thing that happens. First you fetch ingredients from the cupboard, throw away the bad eggs, mix things into bowls, discard a bowl that di

The problem

Question presented to candidate: "We write a query starting with SELECT, but the database does not run it top to bottom. Walk me through the order in which the clauses are logically evaluated, and use it to explain why you can use a column alias in ORDER BY but not in WHERE."

What a strong answer should cover:

The logical order: FROM and JOINs, then WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, and finally LIMIT / OFFSET
Consequences: WHERE cannot see aggregates or SELECT aliases, HAVING filters groups, window functions cannot be filtered in the same query level, ORDER BY can use aliases
The difference between this logical order (what the query means) and the physical plan (what the engine actually does, such as pushing a filter into a scan)
Engine differences: SQLite and MySQL accept some aliases where the standard and PostgreSQL do not
The fix when you need a later value earlier: wrap the query in a subquery or CTE

Code / implementation expected: Optional — explain with one grouped query, ideally showing the alias error and the subquery fix.

Schema used by the examples:

order-of-execution

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who can write a simple SELECT and want to understand why some queries fail with "column does not exist". Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15. MySQL and SQL Server differences are noted where they matter.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running the query; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Think of a cooking recipe card. The title at the top says "Chocolate cake, cut into 8 slices, served on a plate". That is the first thing you read, but it is the last thing that happens. First you fetch i

The solution is hidden so you can try first

12 min read · 9 sections

Run the code

Edit the query and run it against the tables for this question.

SQLPeel one query apart: the rows that survive each step

SQLWindow functions run after WHERE: rank inside a filter, then filter on the rank in an outer query

SQLSQLite accepts a SELECT alias in WHERE and HAVING (PostgreSQL rejects both)

PostgreSQL: the physical plan pushes WHERE into the table scan

EXPLAIN (COSTS OFF)
SELECT d.name AS dept, COUNT(*) AS headcount
FROM employees AS e
JOIN departments AS d ON d.id = e.dept_id
WHERE e.hired >= '2019-01-01'
GROUP BY d.name
HAVING COUNT(*) >= 2
ORDER BY headcount DESC
LIMIT 2;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.