Lesson 3: Deep Dive CTEs

Executive Summary:

After Lesson 2’s brief discussion of CTEs, we’re going to take a deep dive in Common Table Expressions, how they can be utilized, and the powerful things they’re capable of. We can simplify complicated joins, power through hierarchical data via recursive joins, or even utilize multiple CTEs in the same query!

Building Example DB:

-- Create Employees Table
CREATE TABLE Employees (EmployeeID INT, EmployeeName NVARCHAR(MAX), ManagerID INT)
-- Insert values into Employees Table
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID)
VALUES 
    (1, 'CEO', NULL), (2, 'CTO', 1), (3, 'Engineer', 2),
    (4, 'Tech Lead', 2), (5, 'HR Manager', 1), (6, 'HR Associate', 5)

CREATE TABLE Salaries (EmployeeID INT, Salary INT)
INSERT INTO Salaries (EmployeeID, Salary)
VALUES 
    (1, 200000),(2, 180000),(3, 80000),
    (4, 100000),(5, 90000), (6, 60000)

CTE 1: Simplifying Joins

CTEs can be utilized to simplify joins and/or to make them much more user friendly to review. I have a normal query without a CTE and one utilizing a CTE below. How else could this ability be useful?

-- Example without CTE
SELECT 
  E.EmployeeName, S.Salary
FROM 
  Employees E
JOIN
  (SELECT EmployeeID, Salary FROM Salaries) S
ON
  S.EmployeeID = E.EmployeeID
WHERE 
  E.EmployeeID = S.EmployeeID

-- Example WITH CTE
;WITH Salary_CTE AS
(
  SELECT EmployeeID, Salary 
  FROM Salaries
)
SELECT 
  E.EmployeeName, C.Salary
FROM 
  Employees E
JOIN 
  Salary_CTE C 
ON E.EmployeeID = C.EmployeeID

CTE 2: Recursive Lookups

Recursive CTEs allow us to utilize hierarchical data. In our example, we’ll create the reporting structure of the entire company. Without utilizing a CTE, this is not easily visible in SQL.

;WITH Recursive_CTE AS
(
 SELECT EmployeeID, EmployeeName, ManagerID, 1 'Level'
 FROM Employees
 WHERE ManagerID IS NULL
 UNION ALL
 SELECT E.EmployeeID, E.EmployeeName, E.ManagerID, Level + 1
 FROM Employees E
 INNER JOIN Recursive_CTE R
 ON E.ManagerID = R.EmployeeID
)
SELECT 
  EmployeeName, EmployeeID, ManagerID, Level
FROM 
  Recursive_CTE 
ORDER BY Level, EmployeeID

CTE 3: Multiple CTEs in Query

Sometimes, you may need multiple data sources in a query. You can define multiple CTEs for this purpose, depending on what you are attempting to accomplish.

Without using CTEs, multiple subqueries will be needed and those could become complex.
With CTEs, all subqueries and operations can be listed up front and easily modified. Look at the below queries and imagine if multiple values are needed from a secondary source.

;WITH 
Salary_CTE AS (
  SELECT 
   EmployeeID, Salary
  FROM 
   Salaries
),
Manager_CTE AS (
  SELECT 
   EmployeeID, ManagerID
  FROM 
   Employees
)
SELECT 
  E.EmployeeName, S.Salary, M.ManagerID
FROM 
  Employees E
JOIN Salary_CTE S ON E.EmployeeID = S.EmployeeID
JOIN Manager_CTE M ON E.EmployeeID = M.EmployeeID

-- Without CTE
SELECT 
  E.EmployeeName
  , ( SELECT S.Salary FROM Salaries S WHERE S.EmployeeID = E.EmployeeID ) Salary
  , ( SELECT M.ManagerID FROM Employees M WHERE M.EmployeeID = E.EmployeeID ) ManagerID
FROM 
  Employees E