Subquery vs CTE in SQL: When and How to Use Each

A subquery is a SQL query nested inside another statement, while a Common Table Expression (CTE) is a named temporary result set defined with WITH before the main query. Subqueries suit quick, one-off calculations; CTEs break complex queries into readable, reusable steps and are the only way to write recursive queries.

Think of a subquery as scratch math in the margin, and a CTE as a labeled table on the whiteboard that you can refer back to.

What Is a Subquery in SQL?

A subquery can appear inside SELECT, INSERT, UPDATE, or DELETE. The PostgreSQL documentation covers three common forms:

• Scalar subquery: returns a single value, often used for comparisons.
• Derived table: a subquery in the FROM clause that acts as an inline table.
• Predicate subquery: filters rows in WHERE using IN or EXISTS.

Imagine that the following tables exist:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    salary NUMERIC(10, 2) NOT NULL,
    department_id INT REFERENCES departments(department_id),
    manager_id INT REFERENCES employees(employee_id)
);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Marketing');
INSERT INTO employees VALUES
    (1, 'Alice', 9000, 1, NULL),
    (2, 'Bob', 7000, 1, 1),
    (3, 'Carol', 5000, 2, 1),
    (4, 'David', 4000, 2, 3);

-- Scalar subquery: employees earning above the company average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

This returns Alice and Bob, because the average salary is 6,250.

What Is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a named, temporary query declared with WITH that exists only while its statement runs. The CTE below calculates each department's average salary once, then the main query uses it by name:

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, d.department_name, e.salary
FROM employees e
JOIN dept_avg a ON a.department_id = e.department_id
JOIN departments d ON d.department_id = e.department_id
WHERE e.salary > a.avg_salary;

This returns Alice (Engineering) and Carol (Marketing), the top earners in each department.

What Is the Difference Between a Subquery and a CTE?

A subquery is written inline where it is needed, while a CTE is declared upfront and can be referenced several times in the same query.

Feature	Subquery	CTE
Placement	Inline in FROM, WHERE, or SELECT	Declared first with WITH
Readability	Hard to follow when deeply nested	Reads top to bottom
Reusability	Must be repeated each time	Referenced by name multiple times
Recursion	Not supported	Supported with WITH RECURSIVE

How Do Recursive CTEs Work in PostgreSQL?

A recursive CTE uses WITH RECURSIVE to walk hierarchical data, such as a reporting chain. It combines an anchor query with a recursive query that joins back to the CTE, connected by UNION ALL:

WITH RECURSIVE reporting_chain AS (
    SELECT employee_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL          -- anchor: the top manager
    UNION ALL
    SELECT e.employee_id, e.name, r.level + 1
    FROM employees e
    JOIN reporting_chain r ON e.manager_id = r.employee_id
)
SELECT name, level FROM reporting_chain ORDER BY level, name;

This returns Alice (level 1), Bob and Carol (level 2), and David (level 3). Recursion stops when a step returns no new rows.

Key Takeaways

• Use a subquery for a quick, one-off value or filter.
• Use a CTE when a query has several steps or reuses the same result.
• Only a recursive CTE can traverse hierarchies such as org charts or category trees.

Frequently Asked Questions

Are CTEs slower than subqueries in PostgreSQL?

Generally no. Since PostgreSQL 12, a non-recursive CTE referenced once is inlined like a subquery, so performance is the same unless you write AS MATERIALIZED.

Does a subquery in FROM need an alias?

In PostgreSQL 16 and later, the alias is optional. Older PostgreSQL versions, MySQL, and SQL Server require one, so adding an alias keeps your SQL portable.

Can a subquery be recursive?

No. Recursive queries require WITH RECURSIVE.