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.


If you found this interesting and would like to learn more about Common Table Expressions, please take a look at my book on CTE’s at


Posted in CTE, SQL Saturday Tagged with: , , , ,

Leave a Reply

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


SQL Server Performance Tuning

Need help with SQL Server Performance Tuning, contact Steve at Stedman Solutions, LLC for help, or take a look at the performance tuning page at the blog. We can help with tuning a single query or with figuring out why your SQL Server is running slow. Free 30 minute consultation.

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at with my newsletter.
Newsletter signup form.

Stedman Solutions