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.
- By default, MemSQL 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
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;