Back to articles list May 7, 2021 - 8 minutes read What Is the WITH Clause in SQL? Zahin Rahman Nuclear Engineer by day and Data Scientist by night, Zahin is passionate about driving business with data. He has 5+ years of industry experience in energy and power generation and has a master’s degree in Engineering with a focus in Data Science. He loves learning, whether from an industry veteran or a high school science enthusiast. Tags: sql learn sql with 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. Introduction to the SQL WITH Clause The WITH clause in SQL was introduced in standard SQL to simplify complex long queries, especially those with JOINs and subqueries. Often interchangeably called CTE or subquery refactoring, a WITH clause defines a temporary data set whose output is available to be referenced in subsequent queries. It 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 WITH clause is used in queries in which a derived table is unsuitable. Therefore, it is considered a neater alternative to temp tables. Put simply, 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. Some common applications of SQL CTE include: Referencing a temporary table multiple times in a single query. Performing multi-level aggregations, such as finding the average of maximums. Performing an identical calculation multiple times over within the context of a larger query. Using it as an alternative to creating a view in the database. OrderDetailIDOrderIDProductIDQuantity 1102481112 2102484210 310248725 410249149 5102495140 ………… 518104432812 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: QUERY: 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 average_product_quantity 165.493 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: Initiate the WITH Specify the expression name for the to-be-defined query. Optional: Specify column names separated by commas. 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. Write the query required to produce the desired temporary data set. 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. 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. 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 ShipperIDaverage_order_per_employee 29.25 37.555555555555555 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 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. Once you get a good grasp of WITH clauses, you will surprise yourself at how much better your SQL scripts can look! Tags: sql learn sql with You may also like Why the SQL WITH Clause Is Awesome If you are building complex reports in SQL, WITH clauses (aka common table expressions) can be your best friend. Learn how to improve the structure of your SQL queries with CTEs! Read more What Is the SQL WHERE Clause? Are you confused about the SQL WHERE clause? Learn what it does and how it can improve your SQL queries. Read more HAVING vs. WHERE in SQL: What You Should Know To take advantage of SQL’s great power, you must understand HAVING vs. WHERE clauses. How do you use them? What are their differences? Read more Why Window Functions Are Not Allowed in WHERE Clauses Window functions can only appear in SELECT and ORDER BY but not in WHERE clauses. The reason is the logical order in which SQL queries are processed. Read more Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.