A clause to specify Common Table Expressions which allow a subquery to be easily used within a main query.
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