What is the Order of Writing and the Order of Execution in SQL?

Hey everyone! It’s me again🤗

Today, I want to talk about something that’s been driving me crazy — the difference between how we write SQL and how it actually runs.

Why am I writing this? 🤔

Well, I’m always getting into trouble when writing SQL queries because I forget about these orders 😑. So I thought I’d write this blog to help me remember and maybe help you too!💫

Do you know what’s weird about SQL? The way we write it isn’t the same as how the computer runs it.

Mind-blown, right? This has caused me so many headaches! 🤕

How We Write SQL

Usually, when we write SQL, we do it like this,

1. SELECT (pick the columns)
2. FROM (choose the table)
3. WHERE (filter rows)
4. GROUP BY (group stuff)
5. HAVING (filter groups)
6. ORDER BY (sort results)

Seems logical, doesn’t it? ️But that’s not how SQL actually works!

How SQL Really Runs

Here’s the order SQL actually uses,

1. FROM (What table am I using?)
2. WHERE (What rows do I want?)
3. GROUP BY (How am I grouping things?)
4. HAVING (Which groups do I want to keep?)
5. SELECT (What do I want to show?)
6. ORDER BY (How should I sort it?)

Crazy, huh?😮‍💨 Let me show you why this matters.

Let’s say we have a table called ‘employees’ with stuff like id, name, department, and salary.

We want to find the average salary for each department, but only for employees making more than $50,000. Here’s how we might write it,

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE salary > 50000
GROUP BY department

This works fine. But what if we try to use ‘avg_salary’ in the WHERE part?

Suppose we want to retrieve only the departments where the average salary is greater than $50,000. Let’s try.

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE avg_salary > 50000  -- This breaks!
GROUP BY department

It breaks! 💥 Because WHERE happens before SELECT, so ‘avg_salary’ doesn’t exist yet when WHERE is checking things.

To fix this, we need to use HAVING instead,

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000

Have you noticed? The HAVING clause is also evaluated before the SELECT clause. It means that ‘avg_salary’ as an alias doesn't exist when the HAVING clause is processed, just as it doesn't for the WHERE clause. So, how does this work? 🤔

This works because, we can use HAVING with AVG(salary) directly, not because of the alias, but because HAVING can work with aggregate functions after GROUP BY. We can’t use the alias ‘avg_salary’ in the HAVING clause, we must use the full AVG(salary) function again.

Try to guess what this query does,

SELECT department, COUNT(*) as emp_count
FROM employees
WHERE salary > 60000
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY emp_count DESC

Take a moment to think about it. Here’s what’s happening,

1. It starts with the employees table
2. It only looks at employees making more than $60,000
3. It groups these employees by department
4. It only keeps departments with more than 5 of these high-paid employees
5. It counts how many employees are in each of these departments
6. It sorts the results with the highest count first

Cool, right? By understanding the order SQL really uses, we can write better queries and fix them when they go wrong.

Learning about the difference between writing order and running order in SQL has been super helpful for me. It’s helped me write better queries.

I hope this helps you as much as it’s helped me. Happy querying, and may your queries always work the first time! ✨