Multiple CTEs Query Definition In SQL Server

CTE shorthand for Common Table Expression used to simplify derived, nested and complex queries. In contrast, using CTE for writing & breaking complex logic, which is reusable and easily readable. CTE scope to single INSERT/ UPDATE/ DELETE statements, moreover, until the query lasts. Namely, CTE is similar to a view or derived table. CTE is not persisted in the database like temp tables. Additionally, writing recursive queries using CTE has another benefit.

In this tech-recipes post, we are going to learn how to reference multiple CTEs in a single query. For instance, we need to write more than one SELECT statement within a query definition in CTE. This can be accomplished in two ways. Either combining multiple query results using SET operators or writing multiple CTEs and combining them later.

CTE Syntax

;WITH CTE_Expression_Name [ (colname1, colname2, … , n) ]
AS
(CTE - Query Expression)

;WITH CTE_Expression_Name – Specifies name identifier for a CTE. This is mandatory and should be different from other CTE defined in the same scope.

[ (colname1, colname2, … , n) ] – Optional column names returned by CTE query expression. Good practice to have it for every CTE. The number of columns defined in this list should match with the number of columns returned by CTE – query expression.

(CTE – Query Expression) – Includes SELECT statement whose result will be populated as a CTE. Naming a column is compulsory in case of expression, and if the column name is not defined in the second argument.

1.

Multiple CTE Query Reference – Wrong Method

The following query raises an error as this is not the correct way to combine multiple query definitions within CTE. Two individual select statements are not allowed within a CTE query definition. To accomplish this task, we need to use SET operator UNION to combine the result set within CTE.

;WITH Table_View_MetaData_CTE (Object_Name, Object_Id, Create_Date, Object_Type)
AS
(
select name, object_id, create_date, 'Table' from sys.tables --1st Query

select name, object_id, create_date, 'View' from sys.views --2nd Query
)
SELECT Object_Name, Object_Id, Create_Date, Object_Type
FROM   Table_View_MetaData_CTE;

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword ‘select’.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘)’.

2.

Multiple CTE Query Reference – UNION – Right Method

Below query uses UNION ALL SET operator to combine the two select statements. Hence, CTE works with multiple query definitions. We can use JOINS/ and other SET operators within a query definition to combine multiple statements or tables.

;WITH Table_View_MetaData_CTE (Object_Name, Object_Id, Create_Date, Object_Type)
AS
(
select name, object_id, create_date, 'Table' from sys.tables
UNION ALL
select name, object_id, create_date, 'View' from sys.views
)
SELECT Object_Name, Object_Id, Create_Date, Object_Type
FROM   Table_View_MetaData_CTE;

3.

Multiple CTE Query Reference – UNION – Alternate Method

Defining multiple CTEs, each CTE is assigned with a unique name ( Table_MetaData_CTE & View_MetaData_CTE ) and separated by Comma. Later, applying UNION/ JOIN operation to combine multiple CTEs.

;WITH Table_MetaData_CTE (Object_Name, Object_Id, Create_Date, Object_Type) --1st CTE
AS
(
select name, object_id, create_date, 'Table' from sys.tables
),  --Multiple CTEs are separated by Comma
View_MetaData_CTE (Object_Name, Object_Id, Create_Date, Object_Type) --2nd CTE
AS
(
select name, object_id, create_date, 'View' from sys.views
)
SELECT Object_Name, Object_Id, Create_Date, Object_Type
FROM   Table_MetaData_CTE
UNION  
SELECT Object_Name, Object_Id, Create_Date, Object_Type
FROM   View_MetaData_CTE;

4.

Multiple CTE Query Reference – ORDER BY Clause

By design using ORDER BY within CTE is not allowed. This is expected as we can always order the result while selecting the result from CTE. Adding ORDER BY within CTE query definition raises an error as following.

;WITH First_CTE (Num) --1st CTE
AS
    (
    select * FROM (VALUES (2), (1)) AS DT(Num) ORDER BY Num
    ),
     Second_CTE (Num) --2nd CTE
AS
    (
    select * FROM (VALUES (4), (3)) AS DT(Num) ORDER BY Num
    )
SELECT Num FROM First_CTE
UNION ALL
SELECT Num FROM Second_CTE;

Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

As a rule, we can use ORDER BY clause while selecting the results from defined CTE as following. ORDER BY is applied to the entire result of UNION ALL not only to the second CTE View_MetaData_CTE.

;WITH First_CTE (Num) --1st CTE
AS
    (
    select * FROM (VALUES (2), (1)) AS DT(Num)
    ),
     Second_CTE (Num) --2nd CTE
AS
    (
    select * FROM (VALUES (4), (3)) AS DT(Num)
    )
SELECT Num FROM First_CTE
UNION ALL
SELECT Num FROM Second_CTE
ORDER BY  Num; --Correct Use

Summary

In this tech-recipe post we have learned to use multiple CTEs query definition. Using UNION ALL/ Joins operator to combine multiple queries within CTE. If you like this article you may read through Tech-Recipe Database archive post to learn more useful stuff.