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.

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!)

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.

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.