Multiple CTE’s in a single Query

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
(SELECT 'John' UNION SELECT 'Mary' UNION SELECT 'Bill'),

-- Second CTE
minitials (initial) AS
(SELECT 'A' UNION SELECT 'B' UNION SELECT 'C'),

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

-- Using all three
SELECT f.name, m.initial, l.name
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.

multipleCTEs

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 Amazon.com.


Paperback

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

Leave a Reply

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

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Powered by sweet Captcha

Newsletter Signup

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

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA

Common Table Expressions Book

Paperback:

Kindle: