WITH (SQL)
Synopsis
WITH subquery-alias1 AS (subquery1),
subquery-alias2 AS (subquery2),
...
[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 (CTE) allowing you to easily reference a subquery within another query. These subqueries can then be referenced by a “main query,” which must be a SELECT statement. You can define multiple CTEs within a single WITH clause by separating them with commas. A CTE may refer to other CTEs that precede it in the WITH clause.
CTEs support the use of JOIN. You can also use UNION to combine the results of a CTE with the contents of a table.
You can view the query plan for a CTE using the EXPLAIN command.
If you are executing a query that employs a WITH clause over a database driver, you must use a database driver that meets the version requirements. In particular, you must use JDBC version 3.10.1. The ODBC driver included with the latest release also meets this version requirement.
Examples
The following is a basic example that uses a single CTE:
WITH cte1 AS (SELECT * FROM Rooms)
SELECT ID FROM cte1
The following example demonstrates how you can define multiple CTE within a single WITH clause by separating them with commas:
WITH cte1 AS (SELECT Age FROM Occupants),
cte2 AS (SELECT ID FROM Occupants)
SELECT * FROM cte2
The following example demonstrates a CTE that uses a JOIN command:
WITH joincte AS
(SELECT Occupants.Species, Rooms.FavFood FROM Occupants FULL JOIN Rooms ON Occupants.ID=Rooms.ID)
SELECT * FROM joincte
The following example demonstrated a CTE that uses a UNION command:
WITH cte1 AS (SELECT ID FROM Rooms)
SELECT ID FROM cte1 UNION SELECT * FROM cte1
The following example shows how you can reference on subquery from another:
WITH cte1 AS (SELECT * FROM Occupants),
ctewithincte AS (SELECT Age FROM cte1)
SELECT * FROM ctewithincte
