I was recently asked a very interesting question at work. Someone wanted to use the same CTE in two queries the results of which were to be combined using the UNION clause. I was approached to see whether it was feasible.

In order to work out the feasibility, I simply carried out a quick PoC which I am sharing today. It is quite possible to use the same CTE in two queries which are combined via UNION.

USE tempdb;
GO
;WITH EmployeeListCTE (BusinessEntityId, IsCurrent)
AS (SELECT el.BusinessEntityId,
           el.IsCurrent
    FROM (VALUES (1, 1),
                 (2, 0),
                 (3, 1),
                 (4, 1),
                 (5, 1)
         ) AS el(BusinessEntityId, IsCurrent)
   )
SELECT elCTE.BusinessEntityId,
       elCTE.IsCurrent
FROM EmployeeListCTE AS elCTE 
WHERE elCTE.IsCurrent = 1
UNION
SELECT elCTE.BusinessEntityId,
       elCTE.IsCurrent
FROM EmployeeListCTE AS elCTE 
WHERE elCTE.IsCurrent = 0;
GO

/* RESULTS
----------------+-----------
BusinessEntityId|IsCurrent
----------------+-----------
1               |1
3               |1
4               |1
5               |1
2               |0
*/

References:

• Common Table Expressions (CTE) [Link]
• Interesting enhancements to the VALUES Clause in SQL Server 2008 [Link] (by Madhivanan (B|T))

Until we meet next time,

Be courteous. Drive responsibly.