Forum

Forums
SQL
Interview Questions...
"How to Use Common ...
 
Share:
Notifications
Clear all

"How to Use Common Table Expressions (CTEs) in SQL"

 
    Last Post
RSS

Posts: 97
 SQL Admin
Admin
Topic starter
Jan 13, 2025 1:08 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

Introduction
Common Table Expressions (CTEs) simplify SQL queries by allowing you to define temporary result sets that can be referred to within a single statement. They enhance query readability, enable recursion, and make complex queries more manageable. This post will guide you through the basics of CTEs, their syntax, and practical use cases with examples.

What is a CTE?

A Common Table Expression (CTE) is a temporary result set that exists only during the execution of a query. It is defined using the WITH keyword.

Syntax:

sql
 
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;

1. Simple Example of a CTE

Let’s consider a Sales table:

SalesID ProductName Quantity Price
1 Laptop 2 1000
2 Phone 5 500
3 Laptop 3 1000

Using a CTE to calculate total revenue for each product:

sql
 
WITH RevenueCTE AS (
SELECT ProductName, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY ProductName
)
SELECT * FROM RevenueCTE;

Output:

ProductName TotalRevenue
Laptop 5000
Phone 2500

2. Using CTEs to Simplify Complex Queries

Without a CTE:

sql
 
SELECT DepartmentName, COUNT(EmployeeID) AS EmployeeCount
FROM Employees
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Departments
WHERE Location = 'New York'
)
GROUP BY DepartmentName;

With a CTE:

sql
 
WITH NewYorkDepartments AS (
SELECT DepartmentID
FROM Departments
WHERE Location = 'New York'
)
SELECT DepartmentName, COUNT(EmployeeID) AS EmployeeCount
FROM Employees E
INNER JOIN NewYorkDepartments N
ON E.DepartmentID = N.DepartmentID
GROUP BY DepartmentName;

The CTE makes the query easier to read and maintain.

3. Recursive CTEs

Recursive CTEs are useful for traversing hierarchical data, such as organizational charts or category trees.

Example: Employee Hierarchy

Employees Table:

EmployeeID Name ManagerID
1 Alice NULL
2 Bob 1
3 Charlie 2
4 Diana 2

Using a recursive CTE to find all employees under Alice:

sql
 
WITH EmployeeHierarchy AS (
SELECT EmployeeID, Name, ManagerID
FROM Employees
WHERE ManagerID IS NULL

UNION ALL

SELECT E.EmployeeID, E.Name, E.ManagerID
FROM Employees E
INNER JOIN EmployeeHierarchy EH
ON E.ManagerID = EH.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Output:

EmployeeID Name ManagerID
1 Alice NULL
2 Bob 1
3 Charlie 2
4 Diana 2

4. Multiple CTEs in a Query

You can define multiple CTEs in a single query by separating them with commas.

Example: Sales Analysis

sql
 
WITH ProductSales AS (
SELECT ProductName, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductName
),

HighSales AS (
SELECT ProductName
FROM ProductSales
WHERE TotalQuantity > 3
)
SELECT * FROM HighSales;

5. Advantages of Using CTEs

  • Readability: Break down complex queries into smaller, manageable parts.
  • Reusability: Define the logic once and use it multiple times in the query.
  • Recursion: Handle hierarchical or tree-structured data effectively.
  • Performance: Helps the database optimizer create better execution plans.

6. Limitations of CTEs

  • A CTE is not materialized and exists only for the duration of the query.
  • It is not suitable for scenarios where intermediate results need to be persisted.

Conclusion

Common Table Expressions (CTEs) are an invaluable feature for simplifying and optimizing SQL queries. Whether you’re working on complex reports or recursive data, CTEs offer a clean and efficient approach. To explore more SQL techniques and queries, check out our SQL forum.

Topic Tags
SQL CTE examples Common Table Expressions recursive CTE SQL SQL query optimization SQL hierarchical data

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Topic Tags:  SQL CTE examples (1) , Common Table Expressions (1) , recursive CTE SQL (1) , SQL query optimization (1) , SQL hierarchical data (1) ,
Share: