DB Software v7.8

WITH (Common Table Expressions)

Specifies a temporary named result set, referred to as a Common Table Expression (CTE).

Syntax

WITH cte [, ...]

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

Remarks

  • WITH clauses are supported as part of SELECT, INSERT, UPDATE, DELETE, and REPLACE statements.

  • SingleStore DB supports only non-recursive CTEs.recursive tree or graph expansion in a stored procedure or client application

  • By default, SingleStore DB will always attempt to inline CTEs into the parent query; however, you can cache CTEs through materialization to improve the performance of certain complex queries. See Materializing Common Table Expressions for more information.

  • If a CTE defined in the WITH clause is not referenced in the SELECT statement, it has no effect on the execution of the query.

  • The engine variable 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.

Examples

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;
In this section: