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.

Example

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)