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:
- Oracle 9i release 2 and later:
- Microsoft SQL Server 2005 and later:
- IBM DB2 UDB 8 and later:
- PostgreSQL 8.4 and later:
- Sybase 11 and later:
- SQLite 3.8.3 and later:
- Firebird 2.1 and later (the first Open Source DBMS to support recursive queries): http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes210.html#rnfb210-cte
- H2 Database (but only recursive):
- MariaDB is now supporting WITH. https://mariadb.com/kb/en/mariadb/with/
- MySQL 8.0 is finally getting the feature of common table expressions, including recursive CTEs. Here's a blog announcing it: http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/
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
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
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
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
Hope these example and above explanation clear's the concepts of SQL WITH clause.