A Common Table Expression, or CTE, (in SQL) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT
, INSERT
, UPDATE
, or DELETE
statement.
CTEs can be thought of as alternatives to derived tables (subquery), views, and inline user-defined functions.
Common table expressions are supported by DB2, Firebird [1], Microsoft SQL Server, Oracle, PostgreSQL, HyperSQL and H2 (experimental)[2]. Syntax:
WITH [RECURSIVE] with_query [, ...] SELECT...
with_query
looks like
with_query_name [ (column_name [,...]) ] AS (SELECT ...)