What Is the WITH Clause in SQL?

In this article, you will learn about the SQL WITH clause, also known as common table expression (CTE). We will go over some examples to demonstrate some of their use cases in light of their benefits.

Today, I'm here to guide you through the intricacies of the SQL WITH clause, commonly known as Common Table Expression (CTE). If you've faced challenges with lengthy and complex SQL queries, this article will be particularly useful. We'll dive into the WITH clause and show how it can simplify your SQL scripts.

From my experience, using the WITH statement has greatly improved the organization and readability of my queries. It's a valuable tool for anyone looking to refine their SQL code and boost their database management skills.

Let's look at the practical applications and benefits of using CTEs in your SQL queries.

What is the WITH Clause in SQL>

The WITH clause in SQL was introduced in standard SQL to simplify complex long queries. Often called a Common Table Expression (CTE) or subquery refactoring, a WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries.

The best way to learn the WITH clause in SQL is through practice. I recommend LearnSQL.com's interactive Recursive Queries course. It contains over 100 exercises that teach the WITH clause starting with the basics and progressing to advanced topics like recursive WITH queries.

The WITH clause is considered “temporary” because the result is not permanently stored anywhere in the database schema. It acts as a temporary view that only exists for the duration of the query, that is, it is only available during the execution scope of SELECT, INSERT, UPDATE, DELETE, or MERGE statements. It is only valid in the query to which it belongs, making it possible to improve the structure of a statement without polluting the global namespace.

The key advantage of the WITH clause is that it helps organize and simplify long and complex hierarchical queries by breaking them down into smaller, more readable chunks.

The WITH clause was introduced in the SQL standard first in 1999 and is now available in all major RDBMS.

The WITH Clause Example

Let’s see a quick and simple example of the WITH clause below using the OrderDetails table from the well-known Northwind database.

The objective is to return the average quantity ordered per ProductID:

WITH cte_quantity AS (
  SELECT
    SUM(Quantity) as Total
  FROM OrderDetails
  GROUP BY ProductID
)

SELECT
  AVG(Total) average_product_quantity
FROM cte_quantity;

RESULT:

Number of Records: 1

If you were to execute it without the WITH clause and use a subquery instead, the query would look something like this:

QUERY:

SELECT
  AVG(Total) average_product_quantity
FROM
  (SELECT
     SUM(Quantity) as Total
   FROM OrderDetails
   GROUP BY ProductID);

Although you may not see a lot of tangible differences between the two, a broken-down structure that a WITH clause facilitates will be invaluable as your queries scale up in size and hierarchy. We will see an example of this below in the form of a nested WITH clause. You can find more examples in one of our previous articles on the topic – CTEs Explained with Examples.

The WITH Clause Syntax

The general sequence of steps to execute a WITH clause is:

1. Initiate the WITH
2. Specify the expression name for the to-be-defined query.
3. Optional: Specify column names separated by commas.
4. After assigning the name of the expression, enter the AS command. The expressions, in this case, are the named result sets that you will use later in the main query to refer to the CTE.
5. Write the query required to produce the desired temporary data set.
6. If working with more than one CTEs or WITH clauses, initiate each subsequent one separated by a comma and repeat steps 2-4. Such an arrangement is also called a nested WITH clause.
7. Reference the expressions defined above in a subsequent query using SELECT, INSERT, UPDATE, DELETE, or MERGE

The syntax for implementing a WITH clause is shown in the pseudo-code below:

-- CTE
WITH expression_name_1 (column_1, column_2,…,column_n) AS (
  CTE query definition 1
),
expression_name_2 (column_1, column_2,…,column_n) AS (
  CTE query definition 2
)

-- Final query using CTE
SELECT expression_A, expression_B, ...
FROM expression_name_2;

The WITH clause is a drop-in replacement to normal subqueries. The only difference is that you can re-use the same derived result set multiple times in your code when you use the WITH clause to generate a CTE. You cannot do the same with subqueries.

As we see above, the key execution parameters for a WITH clause are:

• WITH: Used to create a CTE, or the temporary data set(s).
• expression_name (column_1, …, column_n): The name of the virtual temporary data set which will be used in the main query, and column_1 to column_n are the column names that can be used in subsequent query steps.
• AS (….): This section defines the query that will populate the CTE expression_name. If implementing a nested CTE, the query within the second AS will likely refer to the first CTE.
• SELECT expression_A, expression_B FROM expression_name: This section specifies the main outer query where the SELECT statement (or INSERT, UPDATE, DELETE, or MERGE statements) is used on one or more of the generated CTEs to subsequently output the intended result.

All of the parameters mentioned above are mandatory. You may choose to use WHERE, GROUP BY, ORDER BY, and/or HAVING clauses as required.

When a query with a WITH clause is executed, first, the query mentioned within the clause is evaluated and the output of this evaluation is stored within a temporary relation. Then, the main query associated with the WITH clause is finally executed using the temporary relation produced.

Nested WITH Clause Example

This example will demonstrate a nested WITH clause using the same OrderDetails table as above. A nested WITH clause, or nested CTEs, involve two CTEs within the same query, the second one referencing the first.

OBJECTIVE: To return the average number of orders, or sales made, by EmployeeID for ShipperID 2 and ShipperID 3.

QUERY:

-- 1st CTE
WITH cte_sales AS (
  SELECT
    EmployeeID,
    COUNT(OrderID) as Orders,
   ShipperID
FROM Orders
GROUP BY EmployeeID, ShipperID),

-- 2nd CTE (nested)
shipper_cte AS (
  SELECT *
  FROM cte_sales
  WHERE ShipperID=2 or ShipperID=3
)

-- Query using CTE
SELECT
  ShipperID, 
  AVG(Orders) average_order_per_employee
FROM shipper_cte
GROUP BY ShipperID;

RESULT:

Number of Records: 2

Here, we calculate the average number of orders per employee but only for ShipperID 2 and ShipperID 3. In the first CTE, cte_sales, the number of orders are counted and grouped by EmployeeID and ShipperID. In the second CTE, shipper_cte, we refer to the first CTE and define the ShipperID conditions using a WHERE clause. Then in the main query, we only refer to the second CTE, shipper_cte, to calculate the average orders per employee by ShipperID.

Further nuances of the syntax associated with SQL WITH clauses and CTEs are detailed out in Module #2 of the Recursive Queries course, which also contains a collection of more advanced walkthrough examples.

Use Cases of the SQL WITH Clause

So, when do you really need to use a WITH Clause? Well, there are a few unique use cases. Most of them are geared towards convenience and ease of query development and maintenance.

The standout applications and associated benefits of SQL CTEs can be summarized as:

• Improves Code Readability– Literate programming is an approach introduced by Donald Kuth, which aims to arrange source code in the order of human logic such that it can be understood with minimal effort by reading it like a novel in a sequential manner. The SQL WITH clause helps do just that by creating virtual named tables and breaking large computations into smaller parts. They can then be combined later in the query in the final SELECT, or another statement, instead of lumping it all into one large chunk.
• Improves Code Maintainability – Going hand in hand with readability is maintainability. As your queries and databases scale up with time, there will always be the need for debugging and troubleshooting – an easier to read code is easier to maintain!
• Alternative to a View– CTEs can substitute for views and can SELECT, INSERT, UPDATE, DELETE, or MERGE This can be particularly useful if you do not have the system rights to create a view object or if you don’t want to create a view just to be used in a single query.
• Overcome Statement Limitations– CTEs help overcome constraints such as SELECT statement limitations, for example, performing a GROUP BY using non-deterministic functions.
• Processing Hierarchical Structures– This is one of the more advanced applications of the CTE and is accomplished through what is known as recursive CTEs. Recursive queries can call on themselves, allowing you to traverse complex hierarchical models. More on this below.

There are a few more use cases of the CTE discussed in one of our previous articles, “When to use CTE”. The Recursive Queries course on LearnSQL.com will help break down the whole topic of the CTE with detailed walkthroughs, to help you master the topic with hands-on exercises.

The Recursive WITH Clause

The examples above use non-recursive WITH clauses. Recursive CTEs, while out of scope for this article, are one of the more advanced functionalities of the WITH clause, which allows referencing itself within that CTE. This makes it considerably easier for users to traverse through complex hierarchical data structures, such as equipment bill of materials (BOM) and organizational charts.

If you are familiar with recursion in programming, the recursive function of the WITH clause similarly incorporates a base case and the recursive step.

Final Thoughts on the SQL WITH Clause

In this article, we have walked through the basic syntax and a few examples of how and when you can define and use WITH clauses or CTEs. To better understand their implementation into your own code, practice is key! For that, I suggest the Recursive Queries course here on LearnSQL.com. In the course, you will find further hands-on examples and unique applications of the WITH clause, with interactive tutorials on how to use CTEs (recursive and non-recursive) in your daily work setting.

The course covers advanced topics and provides a deeper dive into optimizing your SQL queries with CTEs. This hands-on approach will help you master the use of WITH clauses, making your SQL code more efficient and easier to maintain.

Once you get a good grasp of WITH clauses, you will surprise yourself at how much better your SQL scripts can look!