37  SQL With

With statements are also called Common Table Expressions or CTEs. Why should we CTEs instead of views or derived tables (from sub-queries)?

There are two types of CTEs.

  1. Ordinary
  2. Recursive

37.1 With ordinary

Ordinary CTEs behaves like views or derived tables (from sub-queries) but they are more easy to understand compared to them.

They are easy to construct. A simple example is given below. We start with WITH and give name of the temporary result set. Between the parentheses, we write our query. Then after the parentheses, we could select from this Name as if it is view or table.

WITH Name
(
)
select * from Name

See an example below, which make use of self join example of Employee table and query that temp table.

WITH EmployeeManager as
(
SELECT E.EmployeeID
      ,E.LastName AS EmployerLastName
      ,E.FirstName AS EmployerFirstName
      ,E.ReportsTo
      , M.EmployeeID as ManagerID
      , M.LastName AS ManagerLastName
      ,M.FirstName AS ManagerFirstName
  FROM Employee E 
  INNER JOIN Employee M
  ON E.ReportsTo = M.EmployeeID 
)
SELECT * FROM EmployeeManager WHERE ManagerLastName LIKE 'A%'

The above query is functionally equal to following from sub-query.

SELECT * FROM  
(
SELECT E.EmployeeID
      ,E.LastName AS EmployerLastName
      ,E.FirstName AS EmployerFirstName
      ,E.ReportsTo
      , M.EmployeeID as ManagerID
      , M.LastName AS ManagerLastName
      ,M.FirstName AS ManagerFirstName
  FROM Employee E 
  INNER JOIN Employee M
  ON E.ReportsTo = M.EmployeeID 
)
WHERE ManagerLastName LIKE 'A%'

First advantage of the CTEs that we could give meaningful names to our temporary result set like EmployeeManager.

Second advantage of the CTEs is that since result set definition is at the start of the query, where, group by and other statements follow naturally.

Third advantage of the CTEs is that we can iteratively build our SQL from previous result sets in the With statement.

For example, lets say we would like to find which of our sales person is responsible for highest amount of sales.

WITH SALES_EMP_CUS as
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
)
SELECT * FROM SALES_EMP_CUS

We start with finding only sales employee that those employee which support customers. We also add CustomerId to our result set. Here c.SupportRepId IS NOT NULL is gives these values to us.

Then we combine customer Id information with invoice table to bring us total sales according to customer.

WITH SALES_EMP_CUS AS
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
), 
CUSTOMER_SALES_TOTALS AS
(
    SELECT CustomerId, SUM(Total) as total_sales
    FROM Invoice
    GROUP BY CustomerId
)
SELECT * FROM CUSTOMER_SALES_TOTALS

Then we add third query to previous results to find our latest result.

WITH SALES_EMP_CUS AS
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
), 
CUSTOMER_SALES_TOTALS AS
(
    SELECT CustomerId, SUM(Total) as total_sales
    FROM Invoice
    GROUP BY CustomerId
)
--SELECT * FROM CUSTOMER_SALES_TOTALS
SELECT SEC.EmployeeId, SUM(CST.total_sales) AS TotalSalesForEmployee 
FROM SALES_EMP_CUS AS SEC 
INNER JOIN CUSTOMER_SALES_TOTALS AS CST ON CST.CustomerId = SEC.CustomerId
GROUP BY SEC.EmployeeId;

If we need to work on the third result set further, we could also put it in the with statements like below and so on.

WITH SALES_EMP_CUS AS
(
    SELECT 
    e.EmployeeId,e.FirstName,e.LastName,e.Title, c.CustomerId
    FROM Employee e 
    INNER JOIN Customer c on c.SupportRepId = e.EmployeeId
    WHERE c.SupportRepId  IS NOT NULL 
), 
CUSTOMER_SALES_TOTALS AS
(
    SELECT CustomerId, SUM(Total) as total_sales
    FROM Invoice
    GROUP BY CustomerId
),
EMP_SALES AS
(
SELECT SEC.EmployeeId, SUM(CST.total_sales) AS TotalSalesForEmployee 
FROM SALES_EMP_CUS AS SEC 
INNER JOIN CUSTOMER_SALES_TOTALS AS CST ON CST.CustomerId = SEC.CustomerId
GROUP BY SEC.EmployeeId

)
SELECT * FROM EMP_SALES

37.2 With Recursive

Oracle introduced CONNECT BY operator in 1980s for recursive queries. But Recursive CTEs are better and are also standardized in SQL99. See recursive query database support in modern sql site.

A recursive CTE is a CTE that refers to itself in its select part. We could write it as two part sql as below. In the below query we have initial-select and recursive-select parts and these are combined using UNION or UNION ALL operators.

WITH RECURSIVE cte_name AS (
initial-select
UNION [ALL]
recursive-select
)
cte_query

Better example is below.

WITH RECURSIVE cte_name AS (
   SELECT ... FROM TABLE -- initial-select
UNION ALL
   SELECT ... FROM cte_name -- recursive-select, note the self reference to cte_name
) 
SELECT ... FROM cte_name

We write our first initial-select normally. Then, our recursive-select and cte_query part both refer to our cte_name.

** Example 1a: counting numbers** Modified from sqlite documentation, below query counts to 100. Note that in this query, cte_name is num and referenced by three times.

WITH RECURSIVE num(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM num
LIMIT 100
)
SELECT x FROM num;

The first thing to notice,we added RECURSIVE keyword to with. The RECURSIVE is above not required for recursive CTEs in sqlite, Oracle and SQL Server but it is a good idea to include it to make it obvious for further maintainers of your system.

** Example 1b: counting numbers again**

Same example but columns names are not given in cte_name(c1,c2) but given as alias in initial select.

WITH RECURSIVE num AS (
SELECT 1 as x
UNION ALL
SELECT x+1 FROM num
LIMIT 100
)
SELECT x FROM num;

** Example 2 adding numbers**

Lets create another example for adding numbers.

WITH RECURSIVE TotalSum AS ( SELECT 0 AS Count, 0 AS Total UNION ALL SELECT Count + 1, Total + Count FROM TotalSum WHERE Count <= 100 ) SELECT * FROM TotalSum;

37.3 Suggested readings