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.
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 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.
| OrderDetailID | OrderID | ProductID | Quantity |
|---|---|---|---|
| 1 | 10248 | 11 | 12 |
| 2 | 10248 | 42 | 10 |
| 3 | 10248 | 72 | 5 |
| 4 | 10249 | 14 | 9 |
| 5 | 10249 | 51 | 40 |
| … | … | … | … |
| 518 | 10443 | 28 | 12 |
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_quantityAS(SELECTSUM(Quantity) as TotalFROM OrderDetailsGROUP BY ProductID)SELECTAVG(Total) average_product_quantityFROM 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:
SELECTAVG(Total) average_product_quantityFROM(SELECTSUM(Quantity) as TotalFROM OrderDetailsGROUP 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
AScommand. 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
WITHclause. - Reference the expressions defined above in a subsequent query using
SELECT,INSERT,UPDATE,DELETE, orMERGE
The syntax for implementing a WITH clause is shown in the pseudo-code below:
--CTEWITH 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 CTESELECT 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, andcolumn_1tocolumn_nare the column names that can be used in subsequent query steps.AS(….):This section defines the query that will populate the CTEexpression_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 theSELECTstatement (orINSERT,UPDATE,DELETE, orMERGEstatements) 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 CTEWITH cte_salesAS(SELECTEmployeeID,COUNT(OrderID) as Orders,ShipperIDFROM OrdersGROUP BY EmployeeID, ShipperID),--2nd CTE (nested)shipper_cteAS(SELECT *FROM cte_salesWHERE ShipperID=2 or ShipperID=3)--Query using CTESELECTShipperID, AVG(Orders) average_order_per_employeeFROMshipper_cteGROUP BY ShipperID;
RESULT:
Number of Records: 2
| ShipperID | average_order_per_employee |
|---|---|
| 2 | 9.25 |
| 3 | 7.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
WITHclause 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 finalSELECT, 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, orMERGEThis 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
SELECTstatement limitations, for example, performing aGROUP BYusing 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 theRecursive 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!
FAQs
What Is the WITH Clause in SQL? ›
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.
What is the use of with clause in SQL? ›The SQL WITH Clause is used to create temporary tables, which are further used in the main SQL queries. The SQL WITH Clause is used to remove the complexity of reading and debugging nesting SQL subqueries. The usage of WITH Clause is similar to creating tables. You give a name to the table.
What is the difference between CTE and with clause? ›A WITH clause is an optional clause that precedes the SELECT list in a query. The WITH clause defines one or more common_table_expressions. Each common table expression (CTE) defines a temporary table, which is similar to a view definition. You can reference these temporary tables in the FROM clause.
How to use 2 with clause in SQL? ›To have multiple WITH clauses, you do not need to specify WITH multiple times. Rather, after the first WITH clause is completed, add a comma, then you can specify the next clause by starting with <query_name> followed by AS. There is no comma between the final WITH clause and the main SQL query.
Can with clause be used to name a subquery and? ›WITH clause allows us to give a subquery block a name that can be used in multiple places within the main SELECT, INSERT, DELETE or UPDATE SQL query. The name assigned to the subquery is treated as though it was an inline view or a table. It is very helpful when you need the same set of results data multiple times.
How to remove duplicates in SQL? ›- First, we need to find the duplicate rows using the GROUP BY clause or the ROW_NUMBER() function.
- Then, we can use the DELETE statement to remove the duplicate rows.
A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery. The samples in this article use the AdventureWorks2016 database available for download at AdventureWorks sample databases. A subquery can be used anywhere an expression is allowed.
Is it better to use CTE or subquery? ›CTEs are much more readable than subqueries when you're writing a complex report. A CTE can be used many times within a query, whereas a subquery can only be used once. This can make the query definition much shorter, but it won't necessarily result in improved performance.
Is CTE faster than join? ›performance will greatly improve. In my example (I didn't add it here because it is too much tables) the query went from a minute to a second. Furthermore, I noticed in the execution plan of the original query that there is a SORT operator with cost of 95%.
When to use CTE vs temp table SQL? ›If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.
Can we use with twice in SQL? ›
At least if you're running on a SQL Server database, yes it is possible.
Can I use 2 CTE in one query? ›After learning common table expressions or CTEs, a natural question is “Can I use several CTEs in one query?” Yes, you can!
Can I have 2 ORDER BY clauses in SQL? ›You can also ORDER BY two or more columns, which creates a nested sort . The default is still ascending, and the column that is listed first in the ORDER BY clause takes precedence. The following query and Figure 3 and the corresponding query results show nested sorts.
What are the benefits of with clause? ›Benefits of using the WITH Clause
The WITH clause allows you to reduce joins and logically sequence your code. It does this by creating temporary tables (technically they are views) that are usable by your main SQL statement so that you can break your code into smaller and easier to understand snippets.
Subqueries cannot manipulate their results internally, that is, a subquery cannot include the order by clause, the compute clause, or the into keyword. Correlated (repeating) subqueries are not allowed in the select clause of an updatable cursor defined by declare cursor.
What is difference between query and subquery? ›When a query is included inside another query, the Outer query is known as Main Query, and Inner query is known as Subquery. In Nested Query, Inner query runs first, and only once. Outer query is executed with result from Inner query.
How do I remove all duplicates but keep only one in SQL? ›First, the CTE uses the ROW_NUMBER() function to find the duplicate rows specified by values in the first_name , last_name , and email columns. Then, the DELETE statement deletes all the duplicate rows but keeps only one occurrence of each duplicate group.
What is the easiest way to remove duplicates? ›- Select the cells that may have duplicate values. ...
- Go to Data, then click on Remove Duplicates. ...
- Select which columns to check for duplicates. ...
- Click OK and see how many duplicates were removed.
SQL Delete Duplicate Rows using Group By and Having Clause
According to Delete Duplicate Rows in SQL, for finding duplicate rows, you need to use the SQL GROUP BY clause. The COUNT function can be used to verify the occurrence of a row using the Group by clause, which groups data according to the given columns.
The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement. You can use the comparison operators, such as >, <, or =.
What can I use instead of a subquery? ›
Use CTE. A CTE (aka common table expression) is the result set that we create using WITH clause before writing the main query. We can simply use its output as a temporary table, just like a subquery. Similar to subqueries, we can also create multiple CTEs.
What are three subqueries in SQL? ›- Scalar subqueries return a single value, or exactly one row and exactly one column.
- Multirow subqueries return either: One column with multiple rows (i.e. a list of values), or. ...
- Correlated subqueries, where the inner query relies on information obtained from the outer query.
However, subquery appears in the SQL statement multiple times. This method not only incurs additional overhead but also makes the entire statement more complicated. Use a WITH statement to rewrite the statement again.
What is faster than subquery? ›Generally speaking, joins are faster than subqueries, because they can use indexes and other optimization techniques. Subqueries, on the other hand, may require more processing and memory, especially if they return large or complex results.
What is the difference between join and subquery? ›SQL Joins and Subqueries. An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. A subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.
What is the downside of CTE SQL? ›Disadvantages of CTE
CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc. The CTE can only be referenced once by the Recursive member. We cannot use the table variables and CTEs as parameters in stored procedures.
Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler.
What is the alternative to CTE in SQL? ›Nested Queries or Subqueries:
We can use nested queries or subqueries instead of recursive CTE. By nesting queries, we can perform multiple levels of queries to traverse hierarchical structures.
- Tip 1: Add missing indexes. ...
- Tip 2: Check for unused indexes. ...
- Tip 3: Avoid using multiple OR in the FILTER predicate. ...
- Tip 4: Use wildcards at the end of a phrase only. ...
- Tip 5: Avoid too many JOINs.
- Define business requirements first. ...
- SELECT fields instead of using SELECT * ...
- Avoid SELECT DISTINCT. ...
- Create joins with INNER JOIN (not WHERE) ...
- Use WHERE instead of HAVING to define filters. ...
- Use wildcards at the end of a phrase only.
Why not to use temp tables in SQL? ›
Temporary Tables are considered as regular database object, in terms of transaction handling and performance, therefore using many temporary tables in your stored procedures can lead to very poor database performance.
Can you join a table to itself in SQL? ›The SELF JOIN in SQL, as its name implies, is used to join a table to itself. This means that each row in a table is joined to itself and every other row in that table. However, referencing the same table more than once within a single query will result in an error. To avoid this, SQL SELF JOIN aliases are used.
Can SQL multiply two columns? ›All you need to do is use the multiplication operator (*) between the two multiplicand columns ( price * quantity ) in a simple SELECT query. You can give this result an alias with the AS keyword; in our example, we gave the multiplication column an alias of total_price .
Can I group by 2 things in SQL? ›We can use the group by multiple-column technique to group multiple records into a single record. All the records with the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple-column technique.
What is the difference between CTE and view? ›The key thing to remember about SQL views is that, in contrast to a CTE, a view is a physical object in a database and is stored on a disk. However, views store the query only, not the data returned by the query. The data is computed each time you reference the view in your query.
How to query for 2 max salary in SQL? ›Now, to find the second highest salary, we nest the above query into another query as written below. SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT MAX(SALARY) FROM Employee); This query will give you the desired output i.e 12000, which is the second highest salary.
What does coalesce do in SQL? ›The SQL server's Coalesce function is used to handle the Null values. The null values are replaced with user-defined values during the expression evaluation process. This function evaluates arguments in a particular order from the provided arguments list and always returns the first non-null value.
What is the difference between union and join? ›There is a major difference between JOIN and UNION in SQL. Using the JOIN clause, we combine the attributes of two given relations and, as a result, form tuples. Whereas we use the UNION clause when we want to combine the results obtained from two queries. They both combine data differently.
How do I find the last 3 characters in SQL? ›SELECT *FROM yourTableName ORDER BY RIGHT(yourColumnName,3) yourSortingOrder; Just replace the 'yourSortingOrder' to ASC or DESC to set the ascending or descending order respectively. Here is the query to order by last 3 chars.
How to order with 2 criterias in SQL? ›After the ORDER BY keyword, add the name of the column by which you'd like to sort records first (in our example, salary). Then, after a comma, add the second column (in our example, last_name ). You can modify the sorting order (ascending or descending) separately for each column.
Why 1=1 in SQL query? ›
When you use 1=1 as the condition, it essentially means where true . Since the equality condition 1=1 is always true, the WHERE clause does not filter out any records. In other words, it returns all the records from the table without any filtering.
How to select data from CTE in SQL? ›A CTE must be followed by a single SELECT , INSERT , UPDATE , or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view. Multiple CTE query definitions can be defined in a nonrecursive CTE.
When to use CTE in SQL Server? ›- Create a recursive query. ...
- Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
- Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
4. Which of the following clause is mandatorily used in a sub-query? Answer: A. A sub-query is just like any other query which has to start with a SELECT clause.
What are the rules for subquery in SQL? ›- A subquery must be enclosed in parentheses.
- Subqueries that return over one row can only be used with multiple value operators such as the IN operator.
- SQL Server allows you to nest subqueries up to 32 levels.
Which subquery cannot be executed by itself as a separate statement? Explanation: An uncorrelated subquery contains references to the values from the outer query. So, it is dependent on it. Therefore, a correlated subquery cannot be executed by itself as a separate statement.
Is subquery faster than two queries? ›For subqueries and joins, the data needs to be combined. Small amounts can easily be combined in memory, but if the data gets bigger, then it might not fit, causing the need to swap temporary data to disk, degrading performance. So, there is no general rule to say which one is faster.
When to use subquery in SQL example? ›Subqueries (also known as inner queries or nested queries) are a tool for performing operations in multiple steps. For example, if you wanted to take the sums of several columns, then average all of those values, you'd need to do each aggregation in a distinct step.
Can we use with clause in view? ›A view CAN NOT contain WITH clause.
What is the use of with clause in BigQuery? ›The WITH clause is useful primarily for readability, because BigQuery does not materialize the result of the queries inside the WITH clause. If a query appears in more than one WITH clause, it executes in each clause.
What is the difference between using AND on clause in SQL? ›
The USING clause: This allows you to specify the join key by name. The ON clause: This syntax allows you to specify the column names for join keys in both tables.
What is the difference between views and with clause? ›Fundamentally, the definition of a view is saved in the database and can be reused by any query, whereas a WITH clause (or Common Table Expression, or CTE) is tied to one specific query and can only be reused by copying. Otherwise, they will be substantially the same.
What is the benefit of with clause in Oracle? ›The with clause, aka subquery factoring, allows you to tell us "hey, reuse this result over and over in the query". We can factor out a subquery that is used more then once and reuse it -- resulting in a perhaps "better" plan. It can also make the query overall "more readable".
Can we use with clause in PL SQL? ›PL/SQL WITH clause is used for doing the subquery refactoring in complex queries that involve usage of the same result set of a particular query again and again.
What does with clause do in MySQL? ›The WITH clause in MySQL is used to specify a Common Table Expression, a with clause can have one or more comms-separated subclauses.
How to optimize SQL query with WHERE clause? ›- Tip 1: Add missing indexes. ...
- Tip 2: Check for unused indexes. ...
- Tip 3: Avoid using multiple OR in the FILTER predicate. ...
- Tip 4: Use wildcards at the end of a phrase only. ...
- Tip 5: Avoid too many JOINs.
A subquery in a WHERE clause can be used to qualify a column against a set of rows. For example, the following subquery returns the department numbers for departments on the third floor. The outer query retrieves the names of employees who work on the third floor.
How do I join two columns in a table? ›If you'd like to get data stored in tables joined by a compound key that's a primary key in one table and a foreign key in another table, simply use a join condition on multiple columns. In one joined table (in our example, enrollment ), we have a primary key built from two columns ( student_id and course_code ).
In which two cases would you use the using clause? ›USING Clause is used to match only one column when more than one column matches. NATURAL JOIN and USING Clause are mutually exclusive. It should not have a qualifier(table name or Alias) in the referenced columns. NATURAL JOIN uses all the columns with matching names and datatypes to join the tables.
Is join better than WHERE? ›“Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there's no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan.
How to combine two columns in SQL? ›
SQL Server String concatenation allows you to append one string to the end of another string. To display the contents of two columns or more under the name of a single column, you can use the concatenation operator (+).
How to merge string in SQL? ›- ExampleGet your own SQL Server. Add two strings together: SELECT CONCAT('W3Schools', '.com'); ...
- Example. Add 3 strings together: SELECT CONCAT('SQL', ' is', ' fun!' ...
- Example. Add strings together (separate each string with a space character): SELECT CONCAT('SQL', ' ', 'is', ' ', 'fun!'
The coalesce in MySQL can be used to return first not null value. If there are multiple columns, and all columns have NULL value then it returns NULL otherwise it will return first not null value. The syntax is as follows. SELECT COALESCE(yourColumnName1,yourColumnName2,yourColumnName3,.......