Blog post
SQL Executes in Its Own Order (Not the Order You Write It)
You start every query with SELECT. The database starts with FROM. Knowing the execution order makes optimization and debugging far easier.
You write SQL code one way, but does the database actually read it that way?
When we write SQL we usually start with SELECT. But the amusing part is: when the database runs that code, it pays no attention at all to your writing order, or coding order!
Knowing the SQL execution order makes optimizing a query and catching logical errors much easier.
SQL execution: what happens behind the curtain
1. FROM (fixing the source). The engine first looks at where the data lives. As the image shows, it loads all the data from the specified table and completes joins (if any).
2. WHERE (row filtering). Once the table is loaded, unnecessary rows are trimmed according to your condition (Col = 10). This reduces the load on later steps and boosts performance.
3. GROUP BY (grouping the data). The filtered data is clustered into groups based on specific columns. As the image shows, this lays the groundwork for aggregate functions.
4. HAVING (group filtering). HAVING applies a condition over the grouped data (say, SUM(Col2) > 30). It essentially works over the filtered groups.
5. SELECT DISTINCT (displaying the data). After all calculations are done, the engine selects the specific columns. This is the step where DISTINCT removes duplicates. Since SELECT works this late, the earlier steps (like WHERE) cannot recognize any alias you have given.
6. ORDER BY (sorting). At the very end, the output rows are sorted ascending or descending. In the image this is shown with a green arrow.
7. TOP / LIMIT (fixing the count). The absolute last step decides how many rows show up to the user (say, TOP 2). It simply pulls the specified number of rows from the final result.