Understanding Subqueries vs CTEs in SQL (With Examples)

When working with SQL, especially in data analysis, you’ll often need to break down complex queries into manageable pieces. Two powerful tools that help with this are subqueries and Common Table Expressions (CTEs).

This article explains what they are, how they differ, and when to use each—using clear examples.

What is a Subquery?

A subquery is simply a query nested inside another SQL query. It runs first and passes its result to the outer query.
Example:

SELECT name
FROM employees
WHERE salary > (
 SELECT AVG(salary)
 FROM employees
 );

What’s happening here?

The inner query calculates the average salary
The outer query returns employees earning above that average

Types of Subqueries

1. Scalar Subquery
Returns a single value.

 SELECT name
 FROM employees
 WHERE salary > (SELECT AVG(salary) FROM employees);

2. Row Subquery
Returns a single row.

 SELECT *
 FROM employees
 WHERE (department_id, salary) = (
 SELECT department_id, MAX(salary)
 FROM employees
);

3. Column Subquery
Returns a single column.

SELECT name
FROM employees
WHERE department_id IN (
SELECT id FROM departments
 );

4. Correlated Subquery
Depends on the outer query and runs repeatedly.

SELECT name
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e1.department_id = e2.department_id
  );

When Should We Use Subqueries?
Subqueries are useful when:

You need a quick, one-time calculation.
The logic is simple and doesn’t need reuse.
You want to filter data based on aggregated values.

However, they can become hard to read and slow when nested deeply.

What are CTEs (Common Table Expressions)?

A CTE is a temporary result set defined at the start of a query using the WITH keyword. It acts like a named query you can reference later.
Example:

 WITH avg_salary AS (
   SELECT AVG(salary) AS avg_sal
   FROM employees
 )
SELECT name
FROM employees, avg_salary
WHERE employees.salary > avg_salary.avg_sal;

Types and Use Cases of CTEs

1. Simple CTE
Used to simplify complex queries.

WITH high_earners AS (
     SELECT name, salary
     FROM employees
     WHERE salary > 50000
  )
SELECT * FROM high_earners;

2. Recursive CTE
Used for hierarchical data (e.g., organizational charts).

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;

3. Multiple CTEs
You can define multiple CTEs in one query.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
),
filtered AS (
    SELECT e.name, e.salary, e.department_id
    FROM employees e
    JOIN dept_avg d
    ON e.department_id = d.department_id
    WHERE e.salary > d.avg_sal
    )
    SELECT * FROM filtered;

When Should You Use CTEs?

CTEs are ideal when:

Your query is complex and needs structure
You want to reuse logic
You need better readability and debugging
You’re working with hierarchical data (recursion)

Subqueries vs CTEs: Key Comparison

Which One Should You Use?

Use subqueries when:

The query is simple
You only need a quick filter or calculation

Use CTEs when:

The query is complex
You want cleaner, more maintainable code
You need recursion or multiple steps

Final Thoughts
Both subqueries and CTEs are essential SQL tools. The choice between them often comes down to clarity vs simplicity.

If your query is growing complicated or hard to read, that’s usually a sign you should switch to a CTE.

Still learning...

推荐订阅源

DEV Community