Unveiling the Power of CTEs: Using Multiple WITH Clauses in T-SQL

CTEs are temporary named result sets defined within a SQL statement. You can think of them like virtual tables that exist only for the duration of the query. The WITH clause allows you to define these CTEs.

Using Multiple CTEs

When you have a complex query that requires multiple intermediate results, you can define several CTEs using separate WITH clauses. Each CTE can reference data from tables, other CTEs defined earlier within the same WITH clause, or even itself (for recursive CTEs).

Benefits of Multiple CTEs

• Improved code readability: Breaking down complex logic into smaller, named CTEs makes the code easier to understand and maintain.
• Data reusability: You can reference the same CTE multiple times within the main query, reducing redundancy.
• Modularization: Complex calculations or data transformations can be encapsulated within a CTE, making the code more organized.

Here's an example:

WITH SalesPerCustomer AS (
  SELECT CustomerID, SUM(Amount) AS TotalSales
  FROM Orders
  GROUP BY CustomerID
),
AverageSalePerCustomer AS (
  SELECT s.CustomerID, TotalSales / COUNT(*) AS AverageSale
  FROM SalesPerCustomer s
  JOIN Customers c ON s.CustomerID = c.CustomerID
)
SELECT c.CompanyName, AverageSale
FROM AverageSalePerCustomer ac
JOIN Customers c ON ac.CustomerID = c.CustomerID;

In this example, two CTEs are defined:

1. SalesPerCustomer: Calculates the total sales for each customer.
2. AverageSalePerCustomer: Joins the SalesPerCustomer CTE with the Customers table and calculates the average sale per customer.

The main query then selects the company name and average sale from the AverageSalePerCustomer CTE.

By using multiple CTEs, this query becomes more readable and easier to maintain.

-- Simulate employee departments and managers

CREATE TABLE Department (
  DepartmentID INT PRIMARY KEY,
  DepartmentName VARCHAR(50)
);

CREATE TABLE Employee (
  EmployeeID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  DepartmentID INT FOREIGN KEY REFERENCES Department(DepartmentID)
);

CREATE TABLE Manager (
  EmployeeID INT PRIMARY KEY,
  ManagerID INT FOREIGN KEY REFERENCES Employee(EmployeeID)
);

INSERT INTO Department (DepartmentID, DepartmentName)
VALUES (1, 'Sales'), (2, 'Marketing'), (3, 'Engineering');

INSERT INTO Employee (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (101, 'John', 'Doe', 1), (102, 'Jane', 'Smith', 2), (103, 'Mike', 'Lee', 3);

INSERT INTO Manager (EmployeeID, ManagerID)
VALUES (102, 101), (103, 102);

-- Find all employees who don't manage anyone (no rows in Manager for them)

WITH DirectReports AS (
  SELECT ManagerID
  FROM Manager
),
AllManagers AS (
  SELECT m.EmployeeID
  FROM Manager m
  UNION ALL
  SELECT m.EmployeeID
  FROM Manager m
  INNER JOIN DirectReports dr ON dr.ManagerID = m.EmployeeID
)
SELECT e.FirstName, e.LastName
FROM Employee e
LEFT JOIN AllManagers am ON e.EmployeeID = am.EmployeeID
WHERE am.EmployeeID IS NULL;

-- Drops the temporary tables created for this example
DROP TABLE Manager;
DROP TABLE Employee;
DROP TABLE Department;

This code first creates some tables to simulate an employee department structure with managers. Then, it uses two CTEs:

1. DirectReports: Selects manager IDs from the Manager table.
2. AllManagers: Uses a recursive CTE to find all employees in the management chain, starting with direct reports and then traversing upwards.

The main query then joins the Employee table with AllManagers to identify employees who are not listed as managers in the Manager table (meaning they don't manage anyone).

Here's an example using a nested subquery to achieve the same functionality as the previous example with multiple CTEs:

SELECT e.FirstName, e.LastName
FROM Employee e
WHERE NOT EXISTS (
  SELECT 1
  FROM Manager m
  WHERE m.EmployeeID = e.EmployeeID
);

This query uses a nested subquery to check if an employee exists in the Manager table. If not, the outer query selects the employee's information.

Choosing the Right Method:

• Clarity and Readability: For complex logic, CTEs often make the code easier to understand and maintain.
• Performance: For frequently used calculations, CTEs can be more efficient than temporary tables.
• Reusability: If the intermediate result set is needed in multiple queries, views might be a good option.