WITH Clause

08/15/2022 Reading Time: 2 Minutes

The `WITH` clause, commonly called Common Table Expressions (CTE) or subquery factoring, behaves differently than the other clauses I have previously covered. Rather than serving as a clause within a query, a CTE runs an individual query before the parent query is run and temporarily stores the results in a temporary generated table that can be referenced in other CTE’s or the parent query. It is not supported by all database dialects (Only Oracle and Postgresql at this time) and in cases where the expression is not supported, subqueries can be used to replicate most of the functionality. The main differences between CTE’s and subqueries are that CTE’s have recursive capabilities and derived result sets can be used multiple times within the SQL statement rather than once in a nested fashion like subqueries. What is beautiful about this feature is that despite the complex logic described, all you need to do is write WITH cte_name AS ( Query )to create a CTE.

The complexity thankfully lies beneath the surface leaving a SQL statement that is both readable and reusable despite having a SQL query that consists of multiple SQL queries. Take for example a query where we want to attach the MIN, MAX and AVG of the entire data set to our row data. Based on the cars data below:

id make model Revenue
1 Subaru Forester 36241.44
2 Chevrolet Tahoe 21465.89
3 Dodge Caravan 12547.25
4 Chevrolet Cobalt 37408.41
5 Subaru Outback 23707.16

We can do this with a CTE by first calculating the MIN, MAX and AVG of the data set and then including that in our parent query by joining the CTE temporary table along with the model, make and revenue that we can directly query on the cars table.

WITH min_max_make as (
	SELECT make
	, MIN(revenue) AS minimum_revenue
	, MAX(revenue) AS maximum_revenue
	, AVG(revenue) AS average_revenue
	FROM cars
	GROUP BY 1
)
SELECT c.make
	, model
	, revenue
	, minimum_revenue
	, maximum_revenue
	, average_revenue
FROM cars c
JOIN min_max_make mmm ON c.make = mmm.make

Returning:

make model revenue minimum_revenue maximum_revenue average_revenue
Subaru Forester 36241.44 23707.16 36241.44 29974.3
Chevrolet Tahoe 21465.89 21465.89 37408.41 29437.15
Dodge Caravan 12547.25 12547.25 12547.25 12547.25
Chevrolet Cobalt 37408.41 21465.89 37408.41 29437.15
Subaru Outback 23707.16 23707.16 36241.44 29974.3

This result would not be possible with one query alone and the simplicity that CTE’s create with the way it modularizes individual queries. If we had reason to go beyond one CTE in this query, we could do so by comma-separating the CTE’s after the end parentheses and simply writing the name of our CTE’s table followed by the alias and the CTE query statement. We can continue to add as many CTE’s as we would like and even reference them regardless of the CTE order within our parent query.

The blend of simplicity and extended SQL functionality found by using a WITH clause opens up the ability to write complex SQL queries without the need of nesting sub-queries and building multiple tables to get the output you are looking for. Because of this, WITH clauses are extremely common in most SQL queries used by businesses trying to use a flexible method to segment their data and answer a wide variety of questions.

📬 Subscribe to My Newsletter

Get the latest articles and exclusive content straight to your inbox.