The Secret Life of a SQL Query: Order of Execution

Have you ever written a query like this and scratched your head when it failed?

SELECT 
    first_name || ' ' || last_name AS full_name 
FROM users 
WHERE full_name = 'John Doe';

Error: Column "full_name" does not exist.

"But I just defined it!" you scream at your monitor.

The problem isn't your logic; it's your understanding of SQL Order of Execution. Unlike code in Python or JavaScript which reads top-to-bottom, SQL is declarative. You tell the database what you want, and it decides how to get it.

The Real Order of Operations

Here is the order in which a SQL database actually processes your query:

1. FROM / JOIN: First, it needs to know where the data is coming from.
2. WHERE: Then, it filters the rows before doing any calculations.
3. GROUP BY: It groups the filtered rows.
4. HAVING: It filters the groups.
5. SELECT: Finally! This is where it computes your columns and aliases (like full_name).
6. ORDER BY: It sorts the result.
7. LIMIT: It cuts off the result.

That list is a useful mental model, not a full compiler textbook. Real databases add more nuance around subqueries, window functions, set operations, and optimization rewrites. But for day-to-day debugging, this pipeline explains most "why does SQL hate me?" moments.

The "Aha!" Moment

Look at step 2 and step 5. The WHERE clause happens before the SELECT clause.

When the database is processing WHERE full_name = 'John Doe', it hasn't even looked at your SELECT list yet. It has no idea what full_name is.

That's why you have to repeat the logic:

SELECT 
    first_name || ' ' || last_name AS full_name 
FROM users 
WHERE first_name || ' ' || last_name = 'John Doe';

(Or better yet, use a subquery or CTE, but that's a topic for another day!)

Here is the cleaner version using a CTE:

WITH named_users AS (
    SELECT
        first_name || ' ' || last_name AS full_name
    FROM users
)
SELECT *
FROM named_users
WHERE full_name = 'John Doe';

The important idea is that the alias becomes available only after it has been materialized as part of an inner query result.

Three Common Places Execution Order Bites You

1. WHERE vs HAVING

If you filter aggregated results in WHERE, the database complains because grouping has not happened yet.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Use WHERE for row-level filtering before grouping. Use HAVING for group-level filtering after grouping.

2. ORDER BY can often see aliases, but WHERE cannot

This confuses people because the following often works:

SELECT salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

Why? Because ORDER BY runs after SELECT, so the alias already exists. That asymmetry is the source of many beginner errors.

3. Window functions run late in the pipeline

Window functions are evaluated after WHERE, GROUP BY, and HAVING, which is why you usually cannot filter on a window alias directly in the same query.

WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

If you remember only one thing, remember this: when SQL seems inconsistent, it is usually because you are trying to reference a value before the phase that creates it.

Seeing the Matrix: The Abstract Syntax Tree (AST)

How does the database know this? Before it runs anything, it breaks your SQL text into a tree structure called an Abstract Syntax Tree (AST).

It's like diagramming a sentence in English class.

• Statement: SELECT
  • Columns: [full_name]
  • Source: [users]
  • Filter: [full_name = 'John Doe']

On SQL Boy, we have a dedicated AST Viewer tool. You can type any SQL query and see exactly how the database parses it into a tree. This is incredibly useful for understanding complex queries or building your own SQL tools.

Try it yourself

1. Go to the SQL Playground.
2. Type a query.
3. Click the "AST" tab.

You'll see the raw structure of your query. Understanding this structure is the first step to becoming a database expert.

A Practical Debugging Workflow

When a query fails or returns the wrong rows, debug it by matching the problem to the clause stage:

1. Check FROM and JOIN first. Are you starting from the right table, and are joins multiplying rows?
2. Check WHERE. Are filters removing rows before aggregation or window logic has a chance to run?
3. Check GROUP BY and HAVING. Did the query grain change from row-level to group-level?
4. Check SELECT. Are aliases or expressions only being created here?
5. Check ORDER BY and LIMIT. Is the result wrong, or are you just sorting and truncating in a misleading way?

This habit is more useful than memorizing error messages. It gives you a repeatable way to reason about almost any query.

Summary

SQL doesn't run in the order you write it. Remember the pipeline: FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY

Keep this mental model in mind, and those "column not found" errors will become a thing of the past.

Related Articles

• SQL HAVING Clause Explained for the most common follow-up once WHERE vs HAVING starts to click.
• Conditional Aggregation in SQL: A Complete Guide for a more advanced example of row-level logic feeding grouped results.
• Understanding Window Functions in SQL for the next stage where execution order becomes even more important.