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)


© Copyright SS64.com 1999-2014
Some rights reserved