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;

Was this Article Helpful?

Thanks For Your Feedback!