SQL Topics
Common Table Expressions CTE
title: Common Table Expressions CTE
As databases grow larger and queries become more complex, writing readable and maintainable SQL becomes increasingly important.
Consider a query that:
Joins Multiple Tables
Uses Aggregations
Contains Subqueries
Applies FiltersSuch queries can become difficult to read and maintain.
To solve this problem, SQL provides:
Common Table Expressions (CTEs)A CTE allows you to create a temporary named result set that can be referenced within a query.
Think of a CTE as:
Temporary Virtual Tablethat exists only during query execution.
CTEs improve:
Readability
Maintainability
Modularity
Debuggingand are widely used in modern SQL development.
What is a Common Table Expression (CTE)?
A Common Table Expression (CTE) is a temporary result set defined within a SQL statement that can be referenced like a table.
A CTE is created using the:
WITHkeyword.
Unlike a permanent table:
Stored Temporarily
Exists Only During Query Execution
Automatically Removedafter the query finishes.
Simple Definition
A CTE is a temporary named query result that can be used inside a SELECT, INSERT, UPDATE, or DELETE statement.
Why Are CTEs Important?
Before CTEs existed, developers often relied on:
Nested Subqueries
Derived Tables
Complex SQL BlocksThese queries quickly became difficult to understand.
Example:
SELECT *
FROM
(
SELECT *
FROM Employees
) AS Temp;As complexity grows:
Readability Decreases
Maintenance Becomes DifficultCTEs solve this problem.
Benefits of CTEs
CTEs help:
Simplify Complex Queries
Improve Readability
Reduce Repetition
Support Recursion
Improve MaintenanceBasic CTE Syntax
General Syntax:
WITH CTE_Name AS
(
SELECT
Column1,
Column2
FROM TableName
)
SELECT *
FROM CTE_Name;Understanding the Syntax
WITH
Starts the CTE definition.
CTE_Name
Temporary name of the result set.
AS
Associates the query with the CTE.
Parentheses
Contain the CTE query.
Main Query
Uses the CTE like a table.
First CTE Example
Employees Table:
CREATE TABLE Employees
(
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Creating a CTE
WITH HighSalaryEmployees AS
(
SELECT *
FROM Employees
WHERE Salary > 50000
)
SELECT *
FROM HighSalaryEmployees;How It Works
Step 1:
CTE CreatedStep 2:
High Salary Employees SelectedStep 3:
Main Query Uses CTEVisual Representation
Employees Table
↓
HighSalaryEmployees CTE
↓
Final Query ResultCTE vs Subquery
Subquery:
SELECT *
FROM
(
SELECT *
FROM Employees
) Temp;CTE:
WITH EmployeeData AS
(
SELECT *
FROM Employees
)
SELECT *
FROM EmployeeData;CTEs are generally easier to read.
Using Multiple CTEs
Multiple CTEs can be created in one query.
Example:
WITH
HighSalaryEmployees AS
(
SELECT *
FROM Employees
WHERE Salary > 50000
),
LowSalaryEmployees AS
(
SELECT *
FROM Employees
WHERE Salary <= 50000
)
SELECT *
FROM HighSalaryEmployees;CTE with Aggregation
Example:
WITH DepartmentSalary AS
(
SELECT
DepartmentID,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
)
SELECT *
FROM DepartmentSalary;CTE with JOIN
Example:
WITH EmployeeDepartment AS
(
SELECT
E.EmployeeName,
D.DepartmentName
FROM Employees E
JOIN Departments D
ON E.DepartmentID =
D.DepartmentID
)
SELECT *
FROM EmployeeDepartment;CTE with INSERT
Example:
WITH HighSalaryEmployees AS
(
SELECT *
FROM Employees
WHERE Salary > 70000
)
INSERT INTO EmployeeArchive
SELECT *
FROM HighSalaryEmployees;CTE with UPDATE
Example:
WITH SalaryIncrease AS
(
SELECT *
FROM Employees
WHERE DepartmentID = 1
)
UPDATE SalaryIncrease
SET Salary =
Salary + 5000;*(Supported in some database systems)*
CTE with DELETE
Example:
WITH OldEmployees AS
(
SELECT *
FROM Employees
WHERE RetirementYear < 2020
)
DELETE
FROM OldEmployees;Recursive CTE
One of the most powerful features of CTEs.
A Recursive CTE references itself.
Used for:
Hierarchies
Tree Structures
Organizational Charts
Category StructuresWhat is a Recursive CTE?
A Recursive CTE repeatedly executes until a stopping condition is met.
Structure:
Base Query
↓
Recursive Query
↓
Repeat
↓
Stop ConditionRecursive CTE Syntax
WITH CTE_Name AS
(
Base Query
UNION ALL
Recursive Query
)
SELECT *
FROM CTE_Name;Recursive Number Example
WITH Numbers AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Numbers
WHERE Num < 5
)
SELECT *
FROM Numbers;Output:
1
2
3
4
5Employee Hierarchy Example
Employees Table:
| EmployeeID | EmployeeName | ManagerID |
|---|---|---|
| 1 | CEO | NULL |
| 2 | Rahul | 1 |
| 3 | Priya | 2 |
Recursive CTE:
WITH EmployeeHierarchy AS
(
SELECT
EmployeeID,
EmployeeName,
ManagerID
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT
E.EmployeeID,
E.EmployeeName,
E.ManagerID
FROM Employees E
JOIN EmployeeHierarchy EH
ON E.ManagerID =
EH.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;Real-World Example: Organization Chart
Used to retrieve:
CEO
Managers
Employeesin hierarchical order.
Real-World Example: Product Categories
Structure:
Electronics
↓
Mobile Phones
↓
Android PhonesRecursive CTEs retrieve full hierarchy.
Real-World Example: Banking
Used to trace:
Account Relationships
Branch HierarchiesReal-World Example: University System
Used for:
Department Trees
Course PrerequisitesCTE vs Temporary Table
CTE:
Exists During Query
No Physical StorageTemporary Table:
Stored Temporarily
Consumes Storage
Can Be ReusedComparison
| Feature | CTE | Temporary Table |
|---|---|---|
| Temporary | Yes | Yes |
| Physical Storage | No | Yes |
| Reusable | Limited | Yes |
| Recursive Support | Yes | No |
| Simplicity | Higher | Lower |
Advantages of CTEs
Improved Readability
Complex queries become easier to understand.
Better Maintainability
Logic is separated into logical blocks.
Supports Recursion
Major advantage over subqueries.
Reduces Query Complexity
Improves development efficiency.
Easier Debugging
Each CTE can be analyzed independently.
Disadvantages of CTEs
Limited Scope
Exists only during query execution.
Performance Considerations
Large recursive CTEs can become expensive.
Cannot Always Replace Temp Tables
Some scenarios require temporary tables.
Recursion Risks
Incorrect recursion may create infinite loops.
Common Mistakes
Missing Recursive Stop Condition
Can create endless recursion.
Overusing CTEs
Too many CTEs may reduce readability.
Confusing CTEs with Tables
CTEs are temporary.
Ignoring Performance
Large recursive structures can be expensive.
Best Practices
Use Meaningful Names
Example:
HighSalaryEmployees
DepartmentSummary
EmployeeHierarchyKeep CTEs Focused
One logical purpose per CTE.
Use Recursion Carefully
Always include stopping conditions.
Monitor Query Performance
Especially for recursive queries.
Prefer CTEs for Readability
When replacing complex subqueries.
Common Interview Questions
What is a CTE?
A temporary named result set used within a query.
Which keyword creates a CTE?
WITHCan a CTE be recursive?
Yes.
Recursive CTEs are one of its most powerful features.
What is the difference between a CTE and a Temporary Table?
CTEs exist only during query execution, while temporary tables are physically stored.
Why use a CTE instead of a subquery?
To improve readability and maintainability.
Summary
Common Table Expressions (CTEs) are temporary named result sets that simplify complex queries, improve readability, and support recursive operations. They are widely used in enterprise SQL development for hierarchical queries, reporting, analytics, and query organization.
In this lesson, you learned:
- What a CTE is
- CTE Syntax
- CTE vs Subquery
- Multiple CTEs
- Recursive CTEs
- Employee Hierarchies
- Real-world examples
- Advantages and disadvantages
- Best practices
Mastering CTEs is essential because they are one of the most widely used advanced SQL features in modern database systems.
Next Step
Continue to the next lesson:
Recursive CTE →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Common Table Expressions CTE.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, advanced, common
Related SQL Topics