How the WITH Clause Works in SQL

Do you want to master complex SQL queries? Learn how to use the powerful WITH clause!

In SQL, there are simple queries and complex queries. A simple query can take data from a table, apply a filter, and return the data as it is in the source table. A complex query may require complex transformations to the source data or may need to read and join data from multiple tables or views. It may even require using subqueries.

When we are faced with a complex query, using the "divide and conquer" technique can be a good option. The WITH clause in SQL does exactly that: it allows us to divide our query into stages, where each stage consists of creating a kind of table where we approximate the format of the final solution we are looking for. In this article, we will cover what the SQL WITH clause is and how to use it to make complex queries more readable.

The WITH Clause in SQL

The example queries in this article will be based on a table called international_operations. It stores all the export and import operations done by the companies in a country. Here’s a sample of its data:

Before we dig into the SQL WITH clause, I would like to suggest our interactive Recursive Queries course. It has 100+ exercises demonstrating (among other things) how to use the WITH clause. It also covers creating recursive queries, which are very useful in processing hierarchical data structures like trees or graphs.

To start, let’s show a simple example SQL query using the WITH clause. (WITH clauses are also called Common Table Expressions, or CTEs.) Suppose we want to obtain a list of the countries where we exported more than $1,000,000.00 in 2021. The query is:

WITH total_exports_by_country_2021 AS (
	SELECT	
			external_country,
			SUM(amount) AS total
	FROM	international_operations
	WHERE	
			operation_date BETWEEN '1/1/2021' AND '31/1/2021'
	AND	operation_type = 'export'
	GROUP BY external_country
)
SELECT
  external_country,
  total
FROM total_exports_by_country_2021
WHERE total > 1000000;

This is actually made up of two queries. The first query (the CTE, in red) immediately follows the WITH clause; it is executed and the results are put into a virtual table called total_exports_by_country_2021. This is not a regular database table, just a temporary data set that will only be available during query execution. Once the query completes, this data set will be discarded.

An important point to mention about CTEs is that once they are defined, they can be referenced in the rest of the SELECT statement like any other database table. In our example, the blue query makes up the second part of the larger query.

The result of the query is:

Just like any other table, you can use CTEs in a subquery, a JOIN, or in the FROM clause. In other words, you can place the name of the CTE anywhere where you can place the name of a regular database table.

To better understand CTE usage, check out When Should I Use a Common Table Expression (CTE)? and What Is the WITH Clause in SQL?.

Using Multiple CTEs in One Query

CTEs make complex queries more readable. A widely used approach with long queries is to break them into several simple queries that each use a CTE. The CTE names act as natural language descriptors for the data that each one contains. For example, in our previous query, the name of the CTE is total_exports_by_country_2021, which clearly indicates the data contained.

In the next example query, we will create a report showing the names of local import-export companies and their balance (the difference between total yearly export and import amounts) for 2021. Here’s the query:

WITH total_exports_by_company_2021 AS (  -- CTE #1
	SELECT	
local_company,
		SUM(amount) AS total
	FROM	international_operations
	WHERE	operation_date BETWEEN '1/1/2021' AND '31/12/2021'
	     AND operation_type = 'export'
	GROUP BY local_company
),
total_imports_by_company_2021 AS (  -- CTE #2
	SELECT	
local_company,
		SUM(amount) AS total
	FROM	international_operations
	WHERE	operation_date BETWEEN '1/1/2021' AND '31/12/2021'
	     AND operation_type = 'import'
	GROUP BY local_company
)
SELECT 
  exp.local_company, 
  exp.total - imp.total AS balance
FROM 	total_exports_by_company_2021 exp
JOIN 	total_imports_by_company_2021 imp 
ON imp.local_company = exp.local_company

The above query has two CTEs called total_exports_by_company_2021 and total_imports_by_company_2021. You can see only one SQL WITH clause because the syntax of the WITH clause allows us to include many CTEs subqueries. Finally there is the main query, which joins both CTEs and calculates the difference. The result is:

The next example will also use two CTEs, but with a difference: the second CTE will reference the first CTE. We will create a kind of chain of linked queries, where each query will refer to the previous one.

Let’s suppose we want to obtain all the export operations done in 2020 and 2021 by the local companies which experienced an increase in exports in 2021 (vs. 2020). For these operations we want to know the operation_date, local_company, external_company, product_name and amount. This query can be solved in several different ways; let’s see how to solve it using CTEs:

WITH total_exports_by_company_year AS (
	SELECT	
local_company AS company, 
		EXTRACT(YEAR from operation_date) AS year, 
		SUM(amount) AS total_exported
	FROM	international_operations
	WHERE operation_type = 'export'
	GROUP BY local_company, EXTRACT(YEAR from operation_date)
),
companies_raising_exports_in_2021 AS (
	SELECT e20.company
	FROM	total_exports_by_company_year e20
	JOIN 	total_exports_by_company_year e21 
 	ON  e21.year = 2021
	  AND e20.company = e21.company
	  AND e20.total_exported < e21.total_exported
	WHERE	e20.year = 2020
)
SELECT 
io.operation_date, 	
io.product_name,
io.local_company ,	
io.external_company, 		
io.amount
FROM companies_raising_exports_in_2021 r21
JOIN international_operations io 
ON io.local_company = r21.company 
AND EXTRACT(YEAR FROM io.operation_date) IN (2020, 2021)

The name of the first CTE is total_exports_by_company_year, which anticipates that this CTE will have the names of the companies, their total export for each year, and the year.

The second CTE is called companies_raising_exports_in_2021. It will have the names of the companies that exported more in 2021 than in 2020. If we take a look at this CTE, we will notice that the FROM refers to the CTE total_exports_by_company_year. Moreover, it uses a JOIN, thus referencing the CTE twice (once for the 2020 records and again for the 2021 records).

Finally, we have the main query. It refers to the CTE companies_raising_exports_in_2021, since this CTE has the companies that we are interested in showing in the report. The query joins this CTE with the international_operations table, since this table has more data that we want to show. Here is the result:

Want more information? The articles SQL CTEs Explained with Examples and Subquery vs.CTE: A SQL Primer will give you more details. We are not going to cover recursive queries in this article, but they are another thing you can do with the WITH clause in SQL. The article Do it in SQL: Recursive SQL Tree Traversal clearly explains it, using several query examples.

Expand Your SQL Limits Using the WITH clause

In this article, we introduced the SQL WITH clause (aka CTE or common table expression) by showing several SQL examples. Personally, I see WITH as a wildcard clause in SQL. When you have to solve a complex request in a single SQL query, you can think of an intermediate data set between your data source and the result you need to obtain. Then you can create this intermediate data set using the WITH clause. From this intermediate point, you can construct the final query. If you need many intermediate data sets, you can create as many CTEs as necessary.

Finally, I want to remind you about our Recursive Queries course, where you can learn by example and exercise how to create recursive subqueries. Increase your SQL skills, increase your assets!