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
