WITH common_table_expression

A temporary named result set, known as a common table expression (CTE).

Syntax
      [ WITH common_table_expression [ ,...n ] ]

   common_table_expression:
        expression [( column_name [,...n ])]
          AS
            (CTE_query_definition)

This expression is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the same columns.

Examples

WITH InventorySales (Product, Sold) AS
(
   SELECT ProductID, COUNT(*) AS Sold
   FROM Inventory.Sales
   GROUP BY ProductID
)
SELECT SUM(Sold) AS [number of sales made]
FROM InventorySales

"A chic type, a rough type, an odd type - but never a stereotype” ~ Jean-Michel Jarre

Related commands

SELECT
INSERT
UPDATE
DELETE

Equivalent Oracle command:

CREATE TABLE (Temporary)