SQL COMMON TABLE EXPRESSIONS: What They Are, Why They Matter, and How to Use Them

A Common Table Expression (CTE), also known as a "WITH query", is used to break down complex queries into simpler, more manageable parts, enhancing readability of SQL code. It comes in very handy when writing queries that contain complex subqueries. It gives temporary names to the complex subqueries before using it in a bigger query.
There are two kinds of CTEs: Normal and Recursive (Recursive CTE is not covered in this context).

Constructing Common Table Expressions

A CTE query is written using the following syntax:

WITH cte_name (column_name) AS (query)
SELECT * FROM CTE_NAME;

Consider a table called person;

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [e-mail]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [e-mail]     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | [e-mail]         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | [e-mail]     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | [e-mail]  | Male   | Poland          | null        |

The source code, including the query for creating this table, can be found on GitHub.
We want to find the people who have email addresses. We can use a CTE to do this as follows:

-- Create A CTE named 'got_email' to select rows where the email is non-null
-- and select specific columns from the 'got_email' cte
WITH got_email AS (SELECT * FROM person WHERE email IS NOT NULL)
SELECT id,first_name,last_name FROM got_email;

The first line of the CTE defines the CTE called got_email. The CTE selects all the rows from the person table where the where the email column is non-null.
The second line of the query selects a subset of the columns from the CTE got_email.

This query will return the following results:

| id | first_name   | last_name   |
|----|--------------|-------------|
| 1  | Vikki        | Balsillie   |
| 2  | Lorettalorna | Fetteplace  |
| 3  | Ileana       | Guerin      |
| 6  | Maria        | Iddon       |
| 7  | Rog          | McArdell    |

There are certain keywords that cannot be directly used within a CTE. Keywords like INSERT, UPDATE, DELETE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET. CTEs are mainly used for querying and selecting data.

Multiple Common Table Expressions

Multiple Common Table Expressions can be constructed using the syntax below;

WITH
   cte_name1 (column_name) AS (query),
   cte_name2 (column_name) AS (query)
SELECT * FROM cte_name1
UNION ALL
SELECT * FROM cte_name2;

The first CTE is separated from the second CTE with a comma operator and then merged with the SELECT statement outside the CTE definitions.
The multiple CTE can be used for the UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT operations.
We'll use the same table above to explain multiple CTEs. In the table, there are two categories of records(people); people with both email address and car, people with either an email address or car.

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [e-mail]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [e-mail]     | Female | United Kingdom  | Plymouth    |
| 3  | Ileana       | Guerin      | [e-mail]         | Female | Bulgaria        | null        |
| 4  | Walden       | Milmo       | null                        | Male   | Russia          | Ford        |
| 5  | Quincy       | Bromont     | null                        | Male   | China           | Lexus       |
| 6  | Maria        | Iddon       | [e-mail]     | Female | Philippines     | Land Rover  |
| 7  | Rog          | McArdell    | [e-mail]  | Male   | Poland          | null        |

This time, we want to filter the table to only show records where the person have both an email address and a car.
We can do this using multiple CTEs:

--Create two CTEs named 'no_email' and 'no_car'
-- filter rows from the 'person' table except those in 'no_email' and 'no_car' CTEs
WITH no_email AS (SELECT * FROM person WHERE email IS NULL),
no_car AS (SELECT * FROM person WHERE car_make IS NULL)
SELECT * FROM person EXCEPT
(SELECT * FROM no_email UNION ALL
SELECT * FROM no_car);

The first line defines the CTE no_email, which selects all the records from the person table where the email column is null. Separated by a comma operator is the second CTE no_car, which selects all the records from the person table where the where the car_make column is null.
After the definitions of the CTEs is the outer SELECT query which filters rows from the person table excluding those in no_email and no_car CTEs.
The resulting table;

| id | first_name   | last_name   | email                       | gender | country_of_birth | car_make    |
|----|--------------|-------------|-----------------------------|--------|------------------|-------------|
| 1  | Vikki        | Balsillie   | [e-mail]    | Female | Indonesia       | Nissan      |
| 2  | Lorettalorna | Fetteplace  | [e-mail]     | Female | United Kingdom  | Plymouth    |
| 6  | Maria        | Iddon       | [e-mail]     | Female | Philippines     | Land Rover  |

COMPARING CTEs AND TEMPORARY TABLES

You're probably wondering "Well, this sound like temporary table, why not just use a temporary table?". There are some key differences between a CTE and a Temporary table.

1. CTEs are defined using the WITH clause. Temporary tables are created using CREATE TEMPORARY TABLE statements.
2. A CTE is not stored as a physical object in the database, meaning that it is not stored on the disk. A Temporary Table is a physical table that exists temporarily on the database server. It consumes disk space.
3. A CTE is a temporary result set that exists only for the duration of the single queries referencing it. A Temporary Table exist for the duration of a session.
4. CTEs don't necessarily guarantee improved runtime performance. However, it helps organize your queries. Temporary Tables can improve runtime performance, especially when we need to make reference to the same data multiple times.
5. CTEs themselves cannot have indexes directly. However you can create index on the tables the CTE references. You can create indexes on columns of a temporary table, just like you would with a regular table.

CONCLUSION

CTEs can make complex queries more readable by breaking them into smaller, named segments making it easier to understand the logic of the query. It also promotes better code organization.
CTEs provide a solution that doesn't involve physical storage on the database server. By leveraging on the benefits of CTEs, developers can write comprehensible code that can be better optimized when needed.