SQL Deep Dive: Subqueries vs. Common Table Expressions (CTEs)
Introduction
So far, a significant bit of the SQL queries we have covered have been quite straightforward, as we have been dealing with data from a single table. However, moving on, we have encountered more scenarios that require combi9ning multiple steps of logic inside a single query. Subqueries and CTEs allow us to embed one query inside another, thus breaking down complex queries into smaller, more manageable pieces. As will be clearly demonstrated in this article, subqueries and CTEs approach this task quite differently.
What Is a Subquery?
A subquery is basically a query nested inside another SQL query (e.g., inside SELECT, FROM, or WHERE). The inner query (or subquery) returns data that the outer query uses for filtering, calculation, or as a derived table.
Syntax:
SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = ‘Sales’);
Example:
select
score_id,
member_id,
score,
(select round(avg(score),2) from quiz_scores)
from quiz_scores
order by score desc;
When to Use Subqueries
Use subqueries when:
· You need a simple, one-off value for comparison (scalar subquery).
· You want to filter using aggregate logic (e.g., WHERE salary > (SELECT AVG(…))).
· The logic is straightforward and doesn’t require reuse elsewhere in the query.
· You are working in an environment that doesn’t support CTEs (e.g., older MySQL versions).
Caution: Correlated subqueries can be slow on large tables because they execute once per outer row.
What Are CTEs (Common Table Expressions)?
A CTE is a temporary named result set defined using the WITH clause. It exists only during the execution of a single SELECT, INSERT, UPDATE, or DELETE. A CTE makes complex queries more readable by breaking them into named steps.
SYNTAX:
WITH sales_dept AS (
SELECT id FROM departments WHERE name = ‘Sales’
)
SELECT name FROM employees WHERE department_id IN (SELECT id FROM sales_dept);
EXAMPLE:
Get Justuswagura’s stories in your inbox
Join Medium for free to get updates from this writer.
create or replace procedure add_customers
(
p_first_name varchar, — p stands for parameter
p_last_name varchar,
p_email varchar,
p_phone_number varchar,
p_city varchar)
as $$
begin
insert into customers(first_name, last_name, email, phone_number, city)
values (p_first_name, p_last_name, p_email, p_phone_number, p_city);
end;
$$ language plpgsql;
Use CTEs when:
· The same derived table is needed multiple times in one query (no repeated subquery code).
· You want to recursively query self-referential data.
· Readability is a priority — you can name and comment each logical step.
· You are preparing a complex report and want to debug step‑by‑step (run each CTE block independently).
6. Subqueries vs. CTEs: A Clear Comparison
Here is a side‑by‑side breakdown across key dimensions:
Performance nuance:
· In most modern databases (PostgreSQL, SQL Server, Oracle), CTEs are optimization fences — but not always materialized. A CTE referenced once often performs like a subquery.
· Correlated subqueries are slower than an equivalent JOIN + CTE because they run row‑by‑row.
· Recursive CTEs are the only efficient way to traverse hierarchies in SQL.
Readability rule of thumb:
· Use a subquery for simple, single‑use filters (e.g., WHERE column = (SELECT MAX(…))).
· Use a CTE when your query has more than one logical step, or when you need recursion.
7. Summary: When to Choose Which
Final Takeaway
Both subqueries and CTEs are vital tools. Subqueries are concise for simple tasks. On the other hand, CTEs shine in complex, multi‑step logic — especially with recursion or repeated references. Prioritize readability first; you can always test performance later. A clean CTE is easier to optimize than a tangled nest of subqueries.
