WITH (SQL)

A clause to specify Common Table Expressions which allow a subquery to be easily used within a main query.

Synopsis

WITH [subquery-alias] AS ([subquery]) [main-query]

Arguments

Argument	Description
subquery-alias	A alias for the Common Table Expression. Must be a valid identifier.
subquery	The subquery that will form your Common Table Expression. This subquery can then be used in one or more main queries.
main-query	Any SELECT command. Within this query you may use the subquery-alias to utilize the Common Table Expression.

Description

The WITH clause defines a Common Table Expression allowing you to easily reference a subquery within another query. The main query must be a SELECT statement. For example:

WITH cte1 AS (SELECT * FROM Rooms) 
SELECT ID FROM cte1

You can define multiple Common Table Expressions, separated by a comma, within a single WITH clause. For example:

WITH cte1 AS (SELECT Age FROM Occupants), 
cte2 AS (SELECT ID FROM Occupants) 
SELECT * FROM cte2

You can use JOIN or UNION when defining your Common Table Expression. For example:

WITH joincte AS 
 (SELECT Occupants.Species, Rooms.FavFood FROM Occupants FULL JOIN Rooms ON Occupants.ID=Rooms.ID) 
SELECT * FROM joincte

Or,

WITH cte1 AS (SELECT ID FROM Rooms) 
SELECT ID FROM cte1 UNION SELECT * FROM cte1

You can use Common Table Expressions within one another. For example:

WITH cte1 AS (SELECT * FROM Occupants), 
ctewithincte AS (SELECT Age FROM cte1) 
SELECT * FROM ctewithincte

Common Table Expressions are compatible with EXPLAIN. For example:

EXPLAIN WITH cte1 AS (SELECT * FROM Rooms) 
SELECT ID FROM cte1

WITH (SQL)

Synopsis

Arguments

Description

See Also