Subqueries vs. CTEs and When to Use Each

#sql #beginners

SQL offers multiple ways to break down complex problems into manageable steps. Two of the most powerful tools for this are subqueries and Common Table Expressions (CTEs). While they often overlap in functionality, understanding their differences is key to writing efficient, maintainable queries.

A subquery is a query nested inside another query. It executes first, and its result feeds into the outer query. Subqueries can appear in SELECT, FROM, or WHERE clauses.

Example:

SELECT first_name
FROM employees
WHERE employee_id IN (
  SELECT employee_id
  FROM salaries
  WHERE salary > 50000
);

Here, the inner query finds employees with salaries above 50,000, and the outer query retrieves their names.

Types of Subqueries

Scalar subquery → returns a single value.

  SELECT (SELECT AVG(salary) FROM salaries);

Row subquery → returns one row with multiple columns.
Table subquery → returns multiple rows/columns, used in FROM.
Correlated subquery → references columns from the outer query and runs repeatedly.

  SELECT e.first_name
  FROM employees e
  WHERE e.salary > (
    SELECT AVG(salary)
    FROM salaries s
    WHERE s.department_id = e.department_id
  );

When to Use Subqueries

Filtering with complex conditions (WHERE IN, EXISTS).
Returning computed values inline.
Quick one‑off queries where readability isn’t critical.
Situations where you don’t want to define intermediate steps.

What Is a CTE?

A CTE is a temporary named result set defined with WITH. It improves readability by breaking queries into logical blocks.

Example:

WITH high_salary AS (
  SELECT employee_id, salary
  FROM salaries
  WHERE salary > 50000
)
SELECT e.first_name, h.salary
FROM employees e
JOIN high_salary h ON e.employee_id = h.employee_id;

Types and Use Cases of CTEs

Non‑recursive CTEs → simplify complex queries, improve readability.
Recursive CTEs → handle hierarchical data (e.g., org charts, tree structures).
Multiple CTEs → chain transformations step by step.

Use cases:

Reporting pipelines.
Breaking down multi‑stage logic.
Recursive problems like traversing parent‑child relationships.