Execution Order of Statements

Introduction

When grouping, we have seen the following:

• After grouping with GROUP BY, use HAVING to sort, not WHERE.
• The new name of a renamed column with AS is not usable from the HAVING statement.

This is due to the execution order of SQL statements.

• For example, WHERE is always executed before GROUP BY.
  • Therefore, you cannot use WHERE after GROUP BY.
  • But you must use HAVING, which is executed after GROUP BY.

This may seem confusing now.

---

The execution order is as follows:

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY

We will look at this step by step.

---

1. FROM

First, the FROM statement is executed.

With FROM, you specify the table, the source, from which data should be retrieved.

Example:

1. FROM: We retrieve data from the movies table.
2. SELECT: We select all columns.

SELECT
  *
FROM
  movies;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

---

2. JOIN

Next, the JOIN statement is executed (if applicable).

With JOIN, you can merge data from different tables.

We will cover this extensively in a later module.

---

3. WHERE

Next, the WHERE statement is executed (if applicable).

With WHERE, you apply one or more filters to your source data.

Example:

1. FROM: We retrieve data from the movies table.
2. WHERE: We filter for movies with a rating (rank) below 8.
3. SELECT: We select all columns.

SELECT
  *
FROM
  movies
WHERE
  rank < 8;

id	name	year	rank
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
111813	Few Good Men, A	1992	7.5
...	...	...	...

Columns: 4
Rows: 17

---

4. GROUP BY

Next, the GROUP BY statement is executed (if applicable).

With GROUP BY, you group (aggregate) on one or more columns.

Example:

1. FROM: We retrieve data from the movies table.
2. WHERE: We filter for movies with a rating (rank) below 8.
3. GROUP BY: We group by the year column.
4. SELECT: We select:
   • Column year.
   • The number of rows after grouping with COUNT(*) named movies_count.

SELECT
  year,
  COUNT(*) AS movies_count
FROM
  movies
WHERE
  rank < 8
GROUP BY
  year;

year	movies_count
1989	2
1991	1
1984	1
...	...

Columns: 2
Rows: 12

---

5. HAVING

Next, the HAVING statement is executed (if applicable).

With GROUP BY, you group (aggregate) on one or more columns.

Example:

1. FROM: We retrieve data from the movies table.
2. WHERE: We filter for movies with a rating (rank) below 8.
3. GROUP BY: We group by the year column.
4. HAVING: We select only:
   • Results where multiple movies (> 1) were made in a year.
5. SELECT: We select:
   • Column year.
   • The number of rows after grouping with COUNT(*) named movies_count.

SELECT
  year,
  COUNT(*) AS movies_count
FROM
  movies
WHERE
  rank < 8
GROUP BY
  year
HAVING
  COUNT(*) > 1;

year	movies_count
1989	2
2000	3
2001	2
1999	2

Columns: 2
Rows: 4

Note:

• In the SELECT statement, we rename the new column to movies_count.

• This name is therefore not yet available during the HAVING statement.

---

6. SELECT

Next, the SELECT statement is executed.

With SELECT, you select the columns you want to retrieve.

We have seen this in the examples so far.

---

7. ORDER BY

Next, the ORDER BY statement is executed (if applicable).

With ORDER BY, you sort on one or more columns.

Example:

1. FROM: We retrieve data from the movies table.
2. WHERE: We filter for movies with a rating (rank) below 8.
3. GROUP BY: We group by the year column.
4. HAVING: We select only:
   • Results where multiple movies (> 1) were made in a year.
5. SELECT: We select:
   • Column year.
   • The number of rows after grouping with COUNT(*) named movies_count.
6. ORDER BY: We sort:
   • Descending on movies_count

     • Note: the new name movies_count can now be used here.

     • Because the column name movies_count has already been created from SELECT.

   • Ascending on year.

SELECT
  year,
  COUNT(*) AS movies_count
FROM
  movies
WHERE
  rank < 8
GROUP BY
  year
HAVING
  COUNT(*) > 1
ORDER BY
  movies_count DESC,
  year ASC;

---

Summary

• SQL statements are executed in a specific order.

• FROM

  • Retrieve data from a table, the source data.
• JOIN
  • Optional: merge data from multiple tables.
• WHERE
  • Optional: filter on the source data.
• GROUP BY
  • Optional: group on one or more columns.
• HAVING
  • Optional: filter after grouping.
• SELECT
  • Selection of columns.
• ORDER BY
  • Optional: sort on columns.