Multiple CTE’s in a single Query

Download PDF

Common Table Expressions are a great way to simplify complex derived table queries. If it makes sense to break out one derived table or subquery into a CTE, it can make sense to do multiple. To use multiple CTE’s in a single query you just need to finish the first CTE, add a comma, declare the name and optional columns for the next CTE, open the CTE query with a comma, write the query, and access it from a CTE query later in the same query or from the final query outside the CTEs.

-- First CTE
;WITH fnames (name) AS

-- Second CTE
minitials (initial) AS

-- Third CTE
lnames (name) AS
(SELECT 'Anderson' UNION SELECT 'Hanson' UNION SELECT 'Jones')

-- Using all three
SELECT, m.initial,
FROM fnames f
CROSS JOIN lnames AS l
CROSS JOIN minitials m;

Another way that this could be done using SQL Server 2008 or newer is with the row constructor instead of the UNIONs inside of each CTE.

SELECT x FROM (VALUES ('Anderson'), ('Hanson'), ('Jones')) f(x);

This type of row constructor syntax is often used in INSERT statements, but it can also be used in a CTE as shown here:

;WITH Fnames (Name) AS
(SELECT x FROM (VALUES ('John'), ('Mary'), ('Bill')) f(x) ),
Minitials (initial) AS
(SELECT x FROM (VALUES ('A'), ('B'), ('C')) f(x) ),
Lnames (Name) AS
(SELECT x FROM (VALUES ('Anderson'), ('Hanson'), ('Jones')) f(x) )
SELECT F.Name AS FirstName, m.initial, l.Name AS LastName
  FROM Fnames F
 CROSS JOIN Minitials as m
 CROSS JOIN Lnames as L;

Which produces the following output.



Tagged with: , , , ,
One comment on “Multiple CTE’s in a single Query
  1. Kevin Roberts says:

    Great areticle for a quick reference I’ve used this before and my memory wasn’t working today so thanks for setting me straight.

Leave a Reply

Your email address will not be published. Required fields are marked *


Time limit is exhausted. Please reload CAPTCHA.