CTEs in SqL
Common Table Expression (CTE)
The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution. A CTE is defined using a CTE query definition, which specifies the structure and content of the CTE. CTEs often act as a bridge to transform the data in source tables to the format expected by the query.
What is a Common Table Expression (CTE) in SQL :
A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs make queries more readable, reusable, and easier to maintain, especially when working with complex queries or hierarchical data.
Syntax:
WITH cte_name (column1, column2, …) As (
SELECT column1, column2, ….
FROM table_name
WHERE conditions
)
SELECT *
FROM cte_name;
· To reuse the same temporary result set multiple times in a query.
Example 1:
Simple CTE
WITH SalesCTE AS (
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID
)
SELECT *
FROM SalesCTE
WHERE TotalSales > 1000;
· This creates a temporary table SalesCTE to store aggregated sales data and filters products with sales over 1000.
Key Features of CTEs:
1. Temporary Scope: A CTE exists only for the duration of the query it is used in.
2. Improved Readability: It allows breaking down complex queries into smaller, logical components.
3. Reusability: You can reference the CTE multiple times within the query.
4. Recursive Capability: CTEs can be recursive, making them useful for working with hierarchical or tree-like data.
5. Performance: CTEs are not always optimized for performance. Consider Temporary Tables or Materialized for very large datasets.
6. Scope: CTEs exist only within the query where they are defined.
When to Use CTEs:
To improve query clarity and maintainability.
For breaking down complex subqueries.
When performing recursive queries (e.g., organizational hierarchy).
WITH EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID, 1 AS Level
FROM Employees
WHERE ManagerID IS NULL -- Start with top-level manager
UNION ALL
SELECT e.EmployeeID, e.ManagerID, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh
ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;
· This retrieves hierarchical relationships between employees and managers.
A WITH clause is permitted in these contexts:
WITH ... SELECT …
WITH ... UPDATE ...
WITH ... DELETE ...
At the beginning of subqueries (including derived table subqueries):
SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
Only one WITH clause is permitted at the same level. WITH followed by WITH at the same level is not permitted, so this is illegal:
WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...
To make the statement legal, use a single WITH clause that separates the subclauses by a comma:
WITH cte1 AS (...), cte2 AS (...) SELECT ...
However, a statement can contain multiple WITH clauses if they occur at different levels:
WITH cte1 AS (SELECT 1)
SELECT FROM (WITH cte2 AS (SELECT 2) SELECT FROM cte2 JOIN cte1) AS dt;
A WITH clause can define one or more common table expressions, but each CTE name must be unique to the clause. This is illegal:
WITH cte1 AS (...), cte1 AS (...) SELECT ...
To make the statement legal, define the CTEs with unique names:
WITH cte1 AS (...), cte2 AS (...) SELECT ...
Advantages of CTEs over Subqueries?
Readability: Subqueries can become hard to read as they grow. CTEs break them into logical, named blocks.
Reusability: You can reference the same CTE multiple times, avoiding duplication.
Debugging: Easier to debug because each CTE can be tested independently.
Example 1:
Multiple CTEs for Complex Queries
You can define multiple CTEs in a single query and use them together to build a complex result.
WITH SalesCTE AS (
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID
),
HighSalesProducts AS (
SELECT ProductID, TotalSales
FROM SalesCTE
WHERE TotalSales > 1000
),
ProductDetails AS (
SELECT p.ProductID, p.ProductName, h.TotalSales
FROM Products p
INNER JOIN HighSalesProducts h
ON p.ProductID = h.ProductID
)
SELECT *
FROM ProductDetails;
Use Case: Analyze high-sales products and join them with product details for reporting.
Example 2: Recursive CTE to Handle Hierarchical Data
Recursive CTEs are incredibly useful when working with tree-like structures such as employee hierarchies, folder directories, or organizational charts.
Recursive Query for Employee Hierarchy
WITH EmployeeHierarchy AS (
-- Anchor member: Select top-level managers
SELECT EmployeeID, ManagerID, 1 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member: Select employees reporting to the previous level
SELECT e.EmployeeID, e.ManagerID, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh
ON e.ManagerID = eh.EmployeeID
)
SELECT *
FROM EmployeeHierarchy
ORDER BY Level, ManagerID, EmployeeID;
Output: Lists all employees, their hierarchy levels, and managers.
Example 3: CTE for Window Functions
You can combine CTEs with window functions for advanced analytics.
WITH SalesWithRanks AS (
SELECT
ProductID,
SUM(SalesAmount) AS TotalSales,
RANK() OVER (ORDER BY SUM(SalesAmount) DESC) AS SalesRank
FROM Sales
GROUP BY ProductID
)
SELECT *
FROM SalesWithRanks
WHERE SalesRank <= 5;
Output : Identify the top 5 best-selling products.
Example 4: Breaking Down a Nested Query
CTEs make nested queries more readable and easier to debug.
Without CTE (Complex Nested Query):
SELECT
p.ProductID, p.ProductName, SUM(s.SalesAmount) AS TotalSales
FROM Products p
INNER JOIN Sales s
ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING SUM(s.SalesAmount) > 1000;
With CTE (Readable Query):
WITH ProductSales AS (
SELECT
ProductID,
SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID
)
SELECT
p.ProductID, p.ProductName, ps.TotalSales
FROM Products p
INNER JOIN ProductSales ps
ON p.ProductID = ps.ProductID
WHERE ps.TotalSales > 1000;
