SQL - Common Table Expression (CTE)

---

The purpose of the common table expression was to overcome some of the limitations of the subqueries. It also provides a way to query sets of data items that are related to each other by hierarchical relationships, such as organizational hierarchies.

What is CTE (Common Table Expression)?

A CTE is a one-time result set, meaning a temporary table that only exists for the duration of the query. It enables us to refer to the data within the execution scope of a single SELECT, UPDATE, INSERT, DELETE, CREATE, VIEW, OR MERGE statement. CTE is temporary because it cannot be stored anywhere; once the query is executed, it can be lost as soon as possible.

It first came with SQL server 2005. A dba always preferred CTE to use an alternative to a subquery/view. They follow ANSI SQL 99 and are SQL-compliant.

Why do we need CTE?

CTEs can make it easier to manage and write complex queries by making them more readable and simple, like database views and derived tables. We can reuse or rewrite the query by breaking down the complex queries into simple blocks.

Syntax

Following is the syntax of the CTE −

WITH CTE_NAME (column_name) AS (query)
SELECT * FROM CTE_NAME;

The CTE syntax consists of a CTE name, a statement query that defines a column table expression, and an optional column list. We can use it as a view in SELECT, INSERT, UPDATE, DELETE, and merge queries after defining it.

Following diagram is the representation of the query definition −

Note − While writing the CTE query definition; we can use the following commands.

• ORDER BY unless you also use as a TOP clause.
• INTO
• Option clause
• FOR BROWSE

Example

In the following example, let’s see how CTE will work in SQL Server. In this case, we'll use the customer table and perform CTE on it.

Following is the customers table −

+------+----------+------+-----------+--------+
| ID   | NAME     | AGE  | ADDRESS   | SALARY |
+------+----------+------+-----------+--------+
|    1 | Ramesh   |   32 | Ahmedabad |   2000 |
|    2 | Aman     |   23 | Ranchi    |  40000 |
|    3 | kaushik  |   23 | Kota      |   2000 |
|    4 | Chaitali |   25 | Mumbai    |   6500 |
+------+----------+------+-----------+--------+

Following is the query to fetch the details using the CTE −

WITH customer_AGE
AS (SELECT * FROM customers WHERE AGE = 23)
SELECT ID, NAME, AGE FROM customer_AGE;

Output

Since we've created a common table expression called customer AGE, when the above query is executed, we receive an ID, name, and age from CTE, and that age is equal to 23.

+------+---------+------+
| ID   | NAME    | AGE  |
+------+---------+------+
| 2    | Aman    |   23 |
| 3    | kaushik |   23 |
+------+---------+------+

Multiple Common Table Expression (CTE)

Multiple CTEs are used to join together; this simplifies complex SQL queries. Each complex query has its own CTE, which can be referenced and joined outside of the with clause.

Suppose we have some cases where we have to write multiple queries and join them together to see the result; we may use multiple CTEs in this scenario. We have to use the comma operator followed by the CTE name to separate multiple CTE queries and merge them into a single statement.

Syntax

Following is the SQL query for the multiple CTE −

WITH
   CTE_NAME1 (column_name) AS (query),
   CTE_NAME2 (column_name) AS (query)
SELECT * FROM CTE_NAME1
UNION ALL
SELECT * FROM CTE_NAME2;

Note − The multiple CTE can be used for the UNION, UNIONALL, JOIN, INTERSECT, or EXCEPT.

Example

In this example, we defined two CTEs named customers_in_Ranchi and customers_in_Kota. Then the result set of the subquery for these CTEs populated the CTE. Finally, we will use the CTE names in a query that will return all customers who are located in Ranchi and Kota.

Following is the customers table −

+---+-------+---------+----+--------+
|ID |NAME   |ADDRESS  |AGE |SALARY  |
+---+-------+---------+----+--------+
|1  |Rakesh |kota     |24  |30000   |
+---+-------+---------+----+--------+
|2  |Aman   |Ranchi   |23  |50000   |
+---+-------+---------+----+--------+
|3  |Vivek  |kota     |22  |34000   |
+---+-------+---------+----+--------+
|4  |Akash  |Ranchi   |22  |40000   | 
+---+-------+---------+----+--------+
|5  |Rahul  |Hyderabad|23  |38000   |
+---+-------+---------+----+--------+
|6  |Raja   |Patna    |	28 |60000   |
+---+-------+---------+----+--------+
|7  |Sumit  |Ranchi   |25  |40000   |
+---+-------+---------+----+--------+

Following is the SQL CTE query to fetch the details −

WITH
   customers_in_Ranchi AS (SELECT * FROM customers WHERE ADDRESS = 'Ranchi'),
   customers_in_kota AS (SELECT * FROM customers WHERE ADDRESS = 'Kota')
SELECT ID, NAME, ADDRESS FROM customers_in_Ranchi
UNION ALL
SELECT ID, NAME, ADDRESS FROM customers_in_Kota;

Output

Following is the output table that will display ID, name, and Address of the customers using the multiple CTEs.

+----+-------+---------+
| ID | NAME  | ADDRESS |
+----+-------+---------+
| 2  | Aman  | Ranchi  |
| 4  | Akash | Ranchi  |
| 7  | Sumit | Ranchi  |
| 1  | Rakesh| kota    |
| 3  | Vivek | kota    |
+----+-------+---------+

Recursive CTE

A common table expression is a query that keeps referencing a previous result or itself until it returns an empty result. A recursive query continually iterates across a subset of the data when it's run. It simply defines the self-referencing query.

A recursive CTE can only be recursive if it has a UNION ALL statement and a second definition of the query that makes use of the CTE itself in order to be recursive.

Example

In the following SQL query, we understand how CTE will work recursively in SQL Server. Consider the below statement, which generates a series of the first five even numbers −

WITH even_no_CTE(ID, N) AS
(
   SELECT 1, 2
   UNION ALL
   SELECT ID+1, N+2 FROM even_no_CTE WHERE ID<5
)
SELECT * FROM even_no_CTE;

Output

The table that follows recursively retrieves the even number until ID equals 5.

+----+-----+
| ID |	N  |
+----+-----+
| 1  |  2  |
| 2  |  4  |
| 3  |  6  |
| 4  |  8  |
| 5  |  10 |
+----+-----+

Example

In the following example, we are displaying the date recursively from 01 Jan 2023 to 10 Jan 2023.

Following is the query to display the date −

DECLARE @startDate date = '2023-01-01';
Declare @endDate date = '2023-01-10';
WITH RecursiveCte AS
(
   SELECT @startDate AS OrderDate
   UNION ALL
   SELECT DATEADD(d, 1, OrderDate) FROM RecursiveCte
   WHERE DATEADD(d, 1, OrderDate)<= @endDate
)
SELECT * FROM RecursiveCte;

Output

When we execute the above recursive SQL query, we get the following dates −

+------------+
| OrderDate  |
+------------+
| 2023-01-01 |
| 2023-01-02 |
| 2023-01-03 |
| 2023-01-04 |
| 2023-01-05 |
| 2023-01-06 |
| 2023-01-07 |
| 2023-01-08 |
| 2023-01-09 |
| 2023-01-10 |
+------------+

Example

In the following example, we created a recursive CTE that will fetch all the data passed as the parameter of the recursive CTE from the customer table.

WITH recursive_cust (ID, NAME, ADDRESS, AGE) AS 
(
   SELECT ID, NAME, ADDRESS, AGE
   FROM customers
   WHERE SALARY < 40000
   UNION ALL
   SELECT ID, NAME, ADDRESS, AGE
   FROM customers
   WHERE AGE < 25
)
SELECT * FROM recursive_cust;

Output

When the above SQL query is executed, all data from the customers table whose age is less than 25 or salary is less than 40000 will be displayed recursively.

+-----+-------+----------+-----+
| ID  | NAME  | ADDRESS	 | AGE |
+-----+-------+----------+-----+
| 1   | Rakesh| kota	 | 24  |
| 3   | Vivek | kota	 | 22  |
| 5   | Rahul | Hyderabad| 23  |
| 1   | Rakesh| kota	 | 24  |
| 2   | Aman  | Ranchi	 | 23  |
| 3   | Vivek | kota	 | 22  |
| 4   | Akash | Ranchi	 | 22  |
| 5   | Rahul | Hyderabad| 23  |
+-----+-------+----------+-----+

Advantages of CTE

Following is the advantages of the CTE −

• CTE makes the code maintenance easier.

• It increases the readability of the code.

• It increases the performance of the query.

• CTE allows for the simple implementation of recursive queries.

Disadvantages of CTE

Following is the disadvantages of the CTE −

• CTE can only be referenced once by the recursive member.

• The number of columns in the city argument and the number of columns in the query must be the same.

• We cannot use the table variables and CTEs as parameters in a stored procedure.

• CTE members cannot use keyword clauses such as "distinguish," "group by," "have," "join," and so on.

• A CTE can be used in place of a view, but a CTE cannot be nested while views can.