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).
WITH cte [, ...] cte: expression_name[(column_name, ...)] AS select_statement
WITHclauses are supported as part of
- 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
WITHclause is not referenced in the
SELECTstatement, it has no effect on the execution of the query.
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;