SQL Logical processing order

What is the 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 so that they can be used by subsequent stages.

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 selected rows without the rows being collapsed into groups.

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

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;

SQL will process the above query as follows:

1. FROM: We start with the base table: department.
2. INNER JOIN: We join the department table with the employee table based on the ON e.department_id = d.id join condition to create a new virtual table.
3. WHERE: There’s no WHERE clause so this step is skipped.
4. GROUP BY: The rows from the previous virtual table are partitioned into groups so that the rows with the same d.name are put into the same group. After this transformation, we have a new grouped virtual table containing one group for each distinct department name.
5. Aggregate functions: The COUNT(*) expression is evaluated for each group to produce the number of joined rows for each department. These aggregate values can be referenced by subsequent stages (HAVING, SELECT and ORDER BY).
6. HAVING: There’s no HAVING clause so this step is skipped.
7. Window functions: There are no window functions in this query so this step is skipped.
8. SELECT: From the virtual table created by the GROUP by clause, we transform each grouped row by selecting two columns: (1) d.name and (2) a num_employees column which will be the result of the COUNT(*) aggregate function that was computed at stage 5.
9. DISTINCT: There’s no DISTINCT clause so this step is skipped.
10. ORDER BY: The virtual table is ordered so that rows with the highest value for the num_employees column appear at the top.
11. LIMIT/OFFSET: There’s no LIMIT or OFFSET clause so this step is skipped.

At the end of executing of all the clauses, we get our desired virtual table.

Example 2

Let’s take the previous query again:

SELECT d.name, COUNT(*) AS num_employees
FROM department d
         INNER JOIN employee e ON e.deptartment_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:

departments table

id	name
1	IT
2	Finance
3	Business development
4	Marketing

employees 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 Buffet	2

Stage 1: FROM

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

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:

id	name	id	name	department_id
1	IT	100	Martin Fowler	1
1	IT	101	Kent Beck	1
2	Finance	102	Ray Dallio	2
2	Finance	105	Warren Buffet	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:

id	name	id	name	department_id
1	IT	100	Martin Fowler	1
2	Finance	102	Ray Dallio	2
3	Business Development	104	Jeff Bezos	3
4	Marketing	103	Seth Godin	4

Stage 5: Aggregate functions

The COUNT(*) AS num_employees 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 don’t have a ORDER BY clause so no transformation is performed.

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 executed 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