🧠 Understanding SQL Query Execution Order: Not As Straightforward As You Think

While reviewing a PR today, it struck me how SQL queries are written in a logical order that differs from how the database actually executes them.

SELECT name
FROM employees
WHERE age > 30
ORDER BY name;

Simple, right? But did you know SQL doesn't execute the query in this order?

Let’s dive into the actual execution order of SQL queries and why understanding this can save you from common mistakes and boost your database skills.

🧩 The Logical Execution Order of SQL

When SQL runs your query, it doesn't start with SELECT. Instead, it follows this logical order:

Step	Clause	Purpose
1️⃣	`FROM`	Chooses and joins tables
2️⃣	`WHERE`	Filters rows based on conditions
3️⃣	`GROUP BY`	Groups rows into buckets
4️⃣	`HAVING`	Filters groups
5️⃣	`SELECT`	Chooses which columns or expressions to show
6️⃣	`ORDER BY`	Sorts the result
7️⃣	`LIMIT`	Limits the number of rows returned

🧠 Remember: Even though we write SELECT first, SQL runs FROM first!

🔍 Let’s Understand With an Example

SELECT department, COUNT(*) as num_employees
FROM employees
WHERE age > 25
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY num_employees DESC
LIMIT 3;

Step-by-Step Breakdown:

FROM employees
→ Loads the employees table.
WHERE age > 25
→ Filters employees older than 25.
GROUP BY department
→ Groups remaining employees by department.
HAVING COUNT(*) > 5
→ Filters groups (departments) with more than 5 employees.
SELECT department, COUNT(*)
→ Projects the result columns.
ORDER BY num_employees DESC
→ Sorts departments by employee count, descending.
LIMIT 3
→ Picks only top 3 departments.

🚫 Common Gotchas

❌ Using column aliases in WHERE: You can't use a SELECT alias in WHERE because SELECT comes after WHERE.

  SELECT salary * 12 AS annual_salary
  FROM employees
  WHERE annual_salary > 50000; -- ❌ This will fail

✅ Instead, use the expression directly:

  SELECT salary * 12 AS annual_salary
  FROM employees
  WHERE salary * 12 > 50000;

❌ Using aggregate functions in WHERE: You can’t use COUNT(), SUM(), etc. in WHERE — use HAVING instead.

🧠 Why This Matters

🔄 Debugging complex queries becomes easier
🎯 You write more efficient and correct queries
📈 You understand why some queries fail or return unexpected results

📌 Quick Recap: Execution Order

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

Write SQL like a poet, but think like a compiler. 💻❤️

🖼️ Bonus:

Here's a simple visual of the execution order (great for your wall 👇):

      ┌────────────┐
      │   FROM     │
      └────┬───────┘
           ▼
      ┌────────────┐
      │   WHERE    │
      └────┬───────┘
           ▼
      ┌────────────┐
      │  GROUP BY  │
      └────┬───────┘
           ▼
      ┌────────────┐
      │  HAVING    │
      └────┬───────┘
           ▼
      ┌────────────┐
      │  SELECT    │
      └────┬───────┘
           ▼
      ┌────────────┐
      │ ORDER BY   │
      └────┬───────┘
           ▼
      ┌────────────┐
      │  LIMIT     │
      └────────────┘

🧠 Final Thought

If you're just starting out with SQL, internalizing the execution order will make you a more thoughtful and powerful query writer. Next time your query breaks or returns weird results, look back at the steps and see where it’s going off-track!

Abhinav @abhivyaktii