Understanding Subqueries and CTEs in SQL: A Complete Guide
beginners
tutorial
productivity
Working with relational databases often requires breaking down complex problems into manageable parts. Two powerful tools that help achieve this in SQL are subqueries and Common Table Expressions (CTEs). While they may seem similar at first, they serve different purposes and are best used in different scenarios.
This article explores what subqueries and CTEs are, their types, use cases, and how they compare in terms of performance and readability.
What is a Subquery?
A subquery is a query nested inside another SQL query. It is used to perform operations that depend on the result of another query.
Basic Example
**
The inner query calculates the average salary.
The outer query retrieves employees earning above that average.
👉 In simple terms, a subquery provides intermediate results to the main query.
Types of Subqueries
Subqueries can be categorized based on how they are used and how they interact with the outer query.
Single-row Subquery
Returns only one row.
SELECT name
FROM employees
WHERE department_id = (
SELECT id FROM departments WHERE name = 'Sales'
);
Multi-row Subquery
Returns multiple rows and is used with operators like IN, ANY, or ALL.
SELECT name
FROM employees
WHERE department_id IN (
SELECT id FROM departments WHERE location = 'Nairobi'
);
Correlated Subquery
Depends on the outer query and is executed once for each row.
SELECT name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
👉 This is more dynamic but can be slower due to repeated execution.
Nested Subquery
A subquery inside another subquery.
SELECT name
FROM employees
WHERE department_id = (
SELECT id
FROM departments
WHERE location = (
SELECT location
FROM offices
WHERE city = 'Nairobi'
)
);
When Should Subqueries Be Used?
Subqueries are ideal when:
You need a value derived from another query
The logic is simple and contained
You want to filter results dynamically
You’re working with aggregates (AVG, MAX, MIN, etc.)
However, they can become inefficient or hard to read when deeply nested or correlated.
What are CTEs (Common Table Expressions)?
A Common Table Expression (CTE) is a temporary result set defined at the beginning of a query using the WITH keyword. It improves readability and organization, especially in complex queries.
Basic Example
WITH avg_salary AS (
SELECT AVG(salary) AS avg_sal
FROM employees
)
SELECT name
FROM employees, avg_salary
WHERE salary > avg_sal;
👉 Think of a CTE as a temporary named query you can reference within your main query.
Types and Use Cases of CTEs
Non-Recursive CTE
The most common type, used for simplifying complex queries.
WITH department_totals AS (
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
)
SELECT *
FROM department_totals
WHERE total_salary > 50000;
Use case:
Breaking down large queries into readable parts
Recursive CTE
Used to handle hierarchical or tree-structured data.
WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN employee_hierarchy eh
ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
Use case:
Organizational charts
Category hierarchies
Graph traversal
When Should CTEs Be Used?
CTEs are best when:
Queries are complex and need structure
You want to reuse a result multiple times
You need recursive logic
You want to improve readability and maintainability
Subqueries vs CTEs: A Clear Comparison
Readability Subqueries: Can become difficult to read when nested CTEs: Much cleaner and easier to understand
👉 Winner: CTEs
Performance Subqueries: Correlated subqueries can be slow Often re-executed multiple times CTEs: Sometimes optimized better by the database But in some systems, they may not be cached and can behave like inline views
👉 Winner: Depends on the database engine
For repeated logic → CTEs often better
For simple tasks → subqueries are fine
Reusability Subqueries: Cannot be reused easily CTEs: Can be referenced multiple times in the same query
👉 Winner: CTEs
Complexity Handling Subqueries: Good for simple conditions CTEs: Ideal for complex, multi-step logic
👉 Winner: CTEs
Recursion Subqueries: Cannot handle recursion CTEs: Support recursive queries
👉 Winner: CTEs
When to Use Each
Use Subqueries when:
The query is simple and short
You only need the result once
You’re filtering using aggregates
Use CTEs when:
The query is complex or layered
You need better readability
You want to reuse logic
You’re working with hierarchical data
Conclusion
Both subqueries and CTEs are essential tools in SQL, and understanding when to use each can significantly improve your queries.
Subqueries are concise and useful for straightforward operations
CTEs provide structure, clarity, and power for more advanced scenarios
In practice, experienced developers often prefer CTEs for maintainability, especially in large projects—but subqueries still have their place for quick, simple tasks.
Top comments (0)
Subscribe
pic
Add to the discussion
Code of Conduct • Report abuse
profile
The DEV Team
Promoted
Google article image
Architect A Personalized Multi-Agent System with Long-Term Memory
In support of our mission to accelerate the developer journey on Google Cloud, we built Dev Signal — a multi-agent system designed to transform raw community signals into reliable technical guidance by automating the path from discovery to expert creation.
Read more →
Top comments (0)