Abdi Yussuf

Posted on Apr 19

SQL Deep Dive: Subqueries vs. CTEs — Which One Should You Use?

#beginners #database #sql #tutorial

When your data questions get harder, you need to perform calculations before you perform your final SELECT. This is where Subqueries and Common Table Expressions (CTEs) come in.

1. What is a Subquery?

A subquery is a query nested inside another query. It acts as a temporary result set that the outer query uses to filter or calculate data.

The 4 Types of Subqueries

A. Scalar Subquery

Returns exactly one value (one row and one column).

Use Case: When you need a single number, like an average or a maximum, to compare against other rows.

SELECT first_name, mark
FROM exam_results
WHERE mark > (SELECT AVG(mark) FROM exam_results);

B. Multi-Row Subquery

Returns multiple rows but only one column. Usually used with IN, ANY, or ALL.

Use Case: To filter results based on a list of IDs from another table.

SELECT first_name
FROM students
WHERE student_id IN (
    SELECT student_id 
    FROM exam_results 
    WHERE mark < 40
);

C. Correlated Subquery

A subquery that references a column from the outer query. It runs once for every row the outer query processes.

Use Case: When the inner calculation depends on the specific row being looked at (e.g., comparing a student's mark to their class average, not the global average).

SELECT student_id, mark
FROM exam_results e1
WHERE mark > (
    SELECT AVG(mark)
    FROM exam_results e2
    WHERE e1.class_id = e2.class_id
);

D. Table Subquery (Derived Table)

Returns multiple rows and multiple columns. It is used in the FROM clause like a temporary table.

SELECT AVG(temp.total_marks)
FROM (
    SELECT student_id, SUM(mark) AS total_marks
    FROM exam_results
    GROUP BY student_id
) AS temp;

2. What are CTEs (Common Table Expressions)?

A CTE is a temporary result set defined before your main query using the WITH keyword. Think of it like creating a "virtual table" that exists only for that query.

Types & Use Cases

Standard CTE: Best for breaking down long, "spaghetti" code into readable steps
Recursive CTE: Used for hierarchical data, like an organization chart or a category tree

Example of a Standard CTE

WITH HighAchievers AS (
    SELECT student_id, AVG(mark) AS avg_score
    FROM exam_results
    GROUP BY student_id
)
SELECT s.first_name, ha.avg_score
FROM students s
JOIN HighAchievers ha 
    ON s.student_id = ha.student_id
WHERE ha.avg_score > 80;

3. Subqueries vs. CTEs: The Comparison

Feature	Subqueries	CTEs
Readability	Becomes hard to read when deeply nested	Very clean; reads top-to-bottom like a story
Performance	Great for simple, scalar lookups	Modern optimizers treat them similar to subqueries
Reusability	Must be rewritten if used multiple times	Can be referenced multiple times in one query
Recursion	Not supported	Supported (Recursive CTEs)

When to Use Which?

Use a Subquery for very simple, one-off logic inside a WHERE clause
Use a CTE for everything else. If your query has more than one join or involves multiple steps of logic, a CTE will make your life (and your teammates' lives) much easier

Conclusion

While both tools allow you to perform complex logic, CTEs are the winner for modern SQL development due to their organization and readability.

Next time you find yourself nesting a query inside another, try rewriting it as a CTE!

DEV Community