WITH (Common Table Expressions)

Non-recursive (standard) CTE:

WITH cte [, ...]

cte:
    expression_name[(column_name, ...)]
    AS
    select_statement

Non-recursive CTEs:

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;

WITH EmpSal(averageSal) AS
(SELECT AVG(Salary) FROM Employee)
SELECT EmpID, Name, Salary FROM Employee, EmpSal 
WHERE Employee.Salary > EmpSal.averageSal;

WITH ObjectCTE (Name, Id, Date) AS
(SELECT objname, objectid, invoicedate FROM Inventory)
INSERT INTO Itemlist(Name, objectid, createdDate)
SELECT Name, Id, Date FROM ObjectCTE; 

SELECT * FROM Itemlist;

By using the variable materialize_ctes, common table expressions (CTEs) are materialized when queries with CTEs are run. The materialized results can then be referenced multiple times by the parent query. The CTE queries are cached, which results in optimized performance. This is particularly useful for running queries that contain an expensive CTE that produces a relatively small number of rows and is used two or more times.

Enable or disable this functionality by setting the materialize_ctes engine variable, or setting it per session via the MATERIALIZE_CTES session variable:

SET MATERIALIZE_CTES = { 'ALL' | 'AUTO' | 'OFF' };

The following settings are available:

If users can't or don't want to use engine variables, the materialize_cte can be set on a per query basis by adding an OPTION expression at the end of the query:

SELECT * FROM foo OPTION(materialize_ctes="OFF");
SELECT * FROM foo OPTION(materialize_ctes="AUTO");
SELECT * FROM foo OPTION(materialize_ctes="ALL");

Materialization can also be turned off per CTE:

WITH foo as (select with(materialize=off) * from titanic), 
     bar as (select * from titanic) 
     select * from foo, bar;

WITH foo AS (SELECT * FROM t)
SELECT *
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;

WITH foo AS (SELECT * FROM t WHERE t.a = 1 OR t.b = 2)
SELECT *
FROM foo, foo as bar
WHERE foo.a = 1 AND bar.b = 2;