Outdated Version

You are viewing an older version of this section. View current production version.

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.
  • MemSQL supports only non-recursive CTEs.
  • Similar to VIEWs, MemSQL will always attempt to merge CTEs into the query. If this is not possible, the CTE will be materialized into an internal temporary table.
  • 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.

Example

memsql> WITH myCTE AS
(select col from myTable)
select col from myCTE;

memsql> WITH myCTE (colAlias) AS
(select col from myTable)
select colAlias from myCTE;

memsql> 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;