Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
Specifies a temporary named result set, referred to as a Common Table Expression (CTE).
WITH cte [, ...]
cte:
expression_name[(column_name, ...)]
AS
select_statement
WITH
clauses are supported as part of SELECT
, INSERT
, UPDATE
, DELETE
, and REPLACE
statements.WITH
clause is not referenced in the SELECT
statement, it has no effect on the execution of the query.internal_max_cte_depth
defines the maximum number of common table expressions (CTEs) that can be nested. The default value of this variable is 128
.WITH myCTE AS
(select col from myTable)
select col from myCTE;
WITH myCTE (colAlias) AS
(select col from myTable)
select colAlias from myCTE;
WITH orderCTE AS
(select o_orderkey from orders),
lineitemCTE AS
(select l_orderkey from lineitem)
select count(*) from orderCTE join lineitemCTE on o_orderkey = l_orderkey;
WITH foo AS
(WITH bar AS
(SELECT * FROM t) SELECT * FROM bar)
SELECT * FROM foo;