WITH (SQL Server)

SQL Server > DML > WITH

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.
This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
A common table expression can include references to itself. This is referred to as a recursive common table expression.






Examples:

1. Creating a simple common table expression

-- Define the CTE expression name and column list.

WITH Person_CTE (PersonID, PersonName, PersonSalary)

AS
-- Define the CTE query.
(
Select
 PersonID, PersonName, PersonSalary
FROM
 Person
)

-- Define the outer query referencing the CTE name.

SELECT

 *
FROM
 Person_CTE
ORDER BY
 PersonID;

2. Using a recursive common table expression to display multiple levels of recursion

WITH Emp(superiorid, id, username) AS

(
SELECT superiorid, id, username
FROM User
WHERE superiorid IS NULL

UNION ALL

SELECT e.superiorid, e.id, e.username
FROM User AS e
INNER JOIN IS_User AS e1
ON e.superiorid = e1.id
)

SELECT superiorid, id , username

FROM Emp
ORDER BY superiorid;

3. Get all children of parent