WITH

WITH — defines a Common Table Expression (CTE)

Syntax

WITH cte_name AS (
    -- Query defining the CTE
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- Main query referencing the CTE defined earlier
SELECT *
FROM cte_name;

Description

The WITH clause is used to define and name a CTE.

The query that defines the CTE is specified after the AS keyword. This query can include SELECT statements, joins, filtering conditions, and other SQL operations. The result of this query is a table and it forms the temporary result set of the CTE. The CTE can be referenced in the main query that can be a SELECT, INSERT, UPDATE, DELETE, or REPLACE.

Example

WITH outerWith AS (
    SELECT 1 FROM DUAL
)
(
    WITH innerWith AS (
        SELECT 2 FROM dual WHERE 1 IN (SELECT * FROM outerWith)
)
    SELECT * FROM innerWith
);