You are viewing an older version of this section. View current production version.
Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

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;