SQL Operation Order - SQL Execute Logic

How SQL Engine Read Your Query (SQL Operation Order)

Hello friend. Today we talk about SQL operation order. When we code SQL, we type SELECT first. But SQL engine brain not read like that. It read different way from top to bottom.

If you not know real execution order, you will have big bug like using Alias name in WHERE clause and get error.

Real Execution Order Step by Step

1. FROM and JOIN: This is first step. SQL must find table and combine table before do anything.
2. WHERE: SQL filter row data right now. Not match, it throw away.
3. GROUP BY: Now it group data that same same together.
4. HAVING: Filter group data again (WHERE cannot filter group, so we use HAVING here).
5. SELECT: Yes! Step 5 then SQL finally look at SELECT column that we write at line 1.
6. DISTINCT: Remove duplicate row.
7. ORDER BY: Sort A to Z or Z to A.
8. LIMIT / OFFSET: Cut data to show only top 10 or do pagination.

Example SQL Query

Look at this code. We write SELECT on line 1, but SQL brain read FROM first.

SELECT d.department_name, COUNT(s.student_id) AS total_stu    -- Step 5
FROM tbDepartments d                                          -- Step 1
JOIN tbEnrollments s ON d.department_id = s.department_id     -- Step 1
WHERE s.is_active = 1                                         -- Step 2
GROUP BY d.department_name                                    -- Step 3
HAVING COUNT(s.student_id) > 10                               -- Step 4
ORDER BY total_stu DESC                                       -- Step 7
LIMIT 5;                                                      -- Step 8

Why remember this?

Example: In SELECT you put COUNT(s.student_id) AS total_stu. If you try to use total_stu in WHERE clause, it show error! Why? Because step 2 (WHERE) run before step 5 (SELECT). The brain of SQL not yet know the name total_stu.

But you can use total_stu in ORDER BY, because step 7 run after step 5.

That is all friend. Remember this logic, you write SQL no error. Happy coding!