SQL Logical processing order

SQL is a language that lets you use various “clauses” like SELECT, WHERE or GROUP BY to specify the series of transformations you want to apply to a base table.

You begin with a base table and then every clause you specify like WHERE will create a new virtual table and pass that virtual table to the next clause which applies its own transformation to create yet another virtual table. Once all these transformation stages are complete, you end up with the final virtual table. The order in which these clauses are executed is called the logical processing order. Let’s look at that order.

The logical processing order

1. FROM

The base table that the query starts with.

2. JOIN & ON

Rows from multiple tables are combined according to the join condition (ON). Depending on the join type (INNER, LEFT, etc), unmatched rows are kept or discarded.

3. WHERE

The individual rows are filtered according to some condition so that rows that don’t satisfy the condition are discarded.

4. GROUP BY

The rows are partitioned (grouped) into groups that share the same values for the grouping columns.

5. Aggregate functions

Aggregate functions like COUNT() and SUM()are computed for each group so that they can be used by clauses in subsequent stages such as HAVING.

6. HAVING

The groups from the GROUP BY clause are filtered by some condition. Unlike with WHERE, you can use aggregate functions like COUNT() and SUM() to filter the groups. Groups not matching the HAVING condition are discarded.

7. Window functions

Window functions (e.g., ROW_NUMBER(), RANK()) are computed over the rows available at that point in the logical pipeline. If the rows are grouped because of a GROUP BY clause, the window functions are computed over the grouped rows. If the rows aren’t grouped, the window functions are computed over the raw rows.

8. SELECT

The columns are selected for each output row from the virtual table produced from the previous stage.

9. DISTINCT

Duplicate rows are removed.

10. ORDER BY

The result rows are sorted according to one or more columns or expressions.

11. LIMIT/OFFSET

Some rows are skipped or only a limited number of rows are returned.

Example 1

Let’s take this query:

SELECT d.name, COUNT(*) AS num_employees
FROM department d
         INNER JOIN employee e ON e.department_id = d.id
GROUP BY d.name
ORDER BY num_employees DESC;

Remember that SQL is a language for specifying the series of transformations you want to apply to a virtual table. You use clauses like SELECT, FROM, GROUP BY to apply the transformations.

To better understand the logical processing order and to crystallise this mental model of thinking of SQL as a language for transforming virtual tables, let’s take a look at the virtual table we get at each step in the logical processing order.

Let’s assume we have the following tables:

department table

id	name
1	IT
2	Finance
3	Business development
4	Marketing

employee table

id	name	department_id
100	Martin Fowler	1
101	Kent Beck	1
102	Ray Dalio	2
103	Seth Godin	4
104	Jeff Bezos	3
105	Warren Buffett	2

Stage 1: FROM

We begin by building the initial virtual table with the FROM department clause.

Resulting virtual table after transformation:

id	name
1	IT
2	Finance
3	Business development
4	Marketing

Stage 2: `JOIN` & `ON`

We apply the INNER JOIN employee e ON e.department_id = d.id JOIN.

Resulting virtual table after transformation:

d.id	d.name	e.id	e.name	e.department_id
1	IT	100	Martin Fowler	1
1	IT	101	Kent Beck	1
2	Finance	102	Ray Dalio	2
2	Finance	105	Warren Buffett	2
3	Business development	104	Jeff Bezos	3
4	Marketing	103	Seth Godin	4

Stage 3: `WHERE`

We don’t have a WHERE clause so no transformation is performed.

Stage 4: `GROUP BY`

The rows from the virtual table are partitioned into groups so that the rows with the same d.name column value are put into the same group.

Resulting virtual table after transformation:

d.name	rows in group
IT	2
Finance	2
Business development	1
Marketing	1

Stage 5: Aggregate functions

The COUNT(*) expression is evaluated so that the result is available for referencing in a subsequent step.

Resulting virtual table after transformation:

The virtual table remains the same although the result of the aggregate functions are now available in the subsequent steps like SELECT or HAVING.

Stage 6: `HAVING`

We don’t have a HAVING clause so no transformation is performed.

Stage 7: Window functions

We don’t have any window functions so no transformation is performed.

Stage 8: `SELECT`

The SELECT d.name, COUNT(*) AS num_employees clause is evaluated.

Resulting virtual table after transformation:

name	num_employees
IT	2
Finance	2
Business development	1
Marketing	1

Stage 9: `DISTINCT`

We don’t have a DISTINCT clause so no transformation is performed.

Stage 10: `ORDER BY`

We order the rows by the ORDER BY num_employees DESC clause.

Resulting table after transformation

name	num_employees
IT	2
Finance	2
Business development	1
Marketing	1

Stage 11: `LIMIT/OFFSET`

We don’t have a LIMIT or OFFSET clause so no transformation is performed.

Final virtual table

name	num_employees
IT	2
Finance	2
Business development	1
Marketing	1

Other notes

Knowing which clauses are logically processed first by MySQL will help you troubleshoot issues like referencing column aliases in WHERE clauses. SELECT clauses are executed after WHERE clauses so aliases won’t be available in WHERE clauses.
The logical processing order is a logical model, so it’s not necessarily the order of steps the SQL optimizer ends up taking. The optimizer may reorder or skip some steps for performance reasons.

Sources

A Look Into SQL’s Order Of Execution – Aashish Nair

Tagged: SQL