WITH
A WITH clause, used at the beginning of a SQL query, defines aggregations that in turn can be referred to in the main query and in other WITH statements as if they were physical tables.
A WITH statement can be used to create a common table expression (CTE). A CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is not stored as an object, and persists only for the duration of the query.
Syntax
WITH queryName AS (query expression)
[ , ...]
mainQueryExpression
Remarks
• A WITH clause can also refer to a sibling WITH definition (second example below).
• You can first name a query expression and use it within the main query expression by referring to it. If an expression occurs more than once or is complex, moving it out provides clarity.
• The WITH query is run once and the results are stored in the equivalent of a temporary table, which is scanned whenever the results are used. For certain types of queries, this scanning can reduce the burden on the data source.
Example
Suppose that you have a Web service that returns employee data with the following columns:
• employeeNo (the employee’s number)
• employeeName (the employee’s name)
• manager (the employee number of the employee’s manager)
The following query lists all the employees with the details on their respective managers:
WITH us_employees AS
(SELECT employeeNo, emplyeeName, manager FROM employee_webservice WHERE country = 'US')
SELECT e.employeeNo, e.employeeName, 'works for', e.manager,
'who is', m.employeeNo, m.employeeName
FROM us_employees e, us_employees m
WHERE e.manager = m.employeeNo
The advantage of using WITH in this scenario is that it invokes the Web service only once, which in turn enhances query execution performance.
Example (Two WITH Clauses that Do Not Refer to Each Other)
In the following example, X and Y are unique names that do not refer to each other (that is, the value of X is not the same as the value of Y).
WITH
X as (SELECT * From Foo),
Y as (SELECT * From X)
Select * From Y
Example (WITH Statement for Common Table Expressions)
The following example shows the components of the CTE structure: expression name, column list, and query.
WITH Sales_CTE (PersonID, OrderID, Year)
AS
-- Define the CTE query.
(
SELECT PersonID, OrderID, OYEAR(OrderDate) AS Year
FROM Sales.OrderHeader
WHERE PersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT PersonID, COUNT(OrderID) AS Total, Year
FROM Sales_CTE
GROUP BY Year, PersonID
ORDER BY PersonID, Year