In 1992, to make queries reusable SQL introduced views. So once we create a view, it has a name in the database schema so that other queries can use it like a table.

To make it better, in 1999 SQL added WITH clause to define “statement scoped views”. They are not stored in the database schema: instead, they are only valid in the query they belong to. This makes it possible to improve the structure of a statement without polluting the global namespace.

The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Database optimizes the query by treating the query name as either an inline view or as a temporary table

The WITH clause is also known as common table expression (CTE) and subquery factoring.

It 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.

Syntax For The SQL WITH Clause

The following is the syntax of the SQL WITH clause when using a single subquery alias.

WITH <alias_name> AS (sql_subquery_statement)

SELECT column_list FROM <alias_name>[,tablename]

[WHERE <join_condition>]

When using multiple sub-queries syntax can be as below

WITH <alias_name_A> AS (sql_subquery_statement)

<alias_name_B> AS(sql_subquery_statement_from_alias_name_A

or sql_subquery_statement )

SELECT <column_list>

FROM <alias_name_A>, <alias_name_B>, [tablenames]

[WHERE <join_condition>]

In the syntax documentation above, the occurrence of alias_name is a meaningful name you would give to the sub-query after the AS clause. The rest of the queries follow the standard formats for simple and complex SQL SELECT queries.

RDBMS products that support common table expressions:

Example of SQL WITH Clause

Let's consider we have a database AdventureWorks in our SQL server , if you don't have please download from this link : https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks as I will be using this database in the below example, in my case it's AdventureWorks2012 (SQL server 2012 version) (Example's Reference)

So,using WITH , example shows the total number of sales orders per year for each sales representative at Adventure Works Cycles.

USE AdventureWorks2012;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

OUTPUT of the table will be as below

sql-with-clause-min.png

 

Another example : Using a common table expression to limit counts and report averages

The following example shows the average number of sales orders for all years for the sales representatives.

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO

sql-with-clause-example-using-sql-server-min.png

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

For this example , we would have to create one more table, following example shows the hierarchical list of managers and the employees who report to them, so to  begins let' start by creating and populatin the dbo.MyEmployees table

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
	EmployeeID smallint NOT NULL,
	FirstName nvarchar(30)  NOT NULL,
	LastName  nvarchar(40) NOT NULL,
	Title nvarchar(50) NOT NULL,
	DeptID smallint NOT NULL,
	ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

Now, let's create the Query to show the hierarchical list of managers and the employees who report to them

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO

Running the above Query in SQL server using AdventureWorks gives me output as below

sql-server-with-clause-recursive-example-min.png

Using a recursive common table expression to display two levels of recursion

The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO

Output:

sql-with-clause-example-2-min.png

Hope these example and above explanation clear's the concepts of SQL WITH clause.