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.
• 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 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!