Common Table Expressions (CTEs) vs. Subqueries in SQL

In partnership with

When working with SQL, you'll often need to break down complex queries into manageable pieces. Two powerful tools for this are Common Table Expressions (CTEs) and subqueries. While both help with organizing queries, they have different use cases and performance implications. Let’s dive into what they are, when to use them, and how they compare.

What is a Subquery?

A subquery is a query nested inside another SQL statement. It can be used in SELECT, FROM, or WHERE clauses to filter, aggregate, or compute values dynamically.

Example: Subquery in the SELECT Clause

Let’s say we have a sales table:

We want to retrieve each sale along with the total amount spent by the same customer. We can use a subquery:

SELECT sale_id,
       customer_id,
       amount,
       (SELECT SUM(amount) FROM sales s2 WHERE s1.customer_id = s2.customer_id) AS total_spent
FROM sales s1;

When to Use Subqueries

• When you need a quick lookup inside another query.

• When the computation is simple and doesn’t need reuse.

• When you need a scalar (single-value) result in a SELECT statement.

Beyond ChatGPT, are you really using AI??? DOn’t be a dinosaur. Get on the AI train. Get The AI Report

What is a Common Table Expression (CTE)?

A CTE is a temporary named result set defined using the WITH clause. It improves readability and reusability, especially for complex queries.

Example: CTE for Readability

Let’s rewrite the previous query using a CTE:

WITH CustomerTotals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
)
SELECT s.sale_id,
       s.customer_id,
       s.amount,
       c.total_spent
FROM sales s
JOIN CustomerTotals c ON s.customer_id = c.customer_id;

When to Use CTEs

• When you need to use the same subquery multiple times.

• When breaking down complex queries for readability.

• When using recursive queries (which subqueries can’t do).

Performance Comparison

CTEs and subqueries often produce the same results, but their performance can differ.

Final Thoughts: Which One Should You Use?

• Use subqueries for quick, simple lookups where reusability isn’t a concern.

• Use CTEs for readability, reusability, and when dealing with complex queries.

• Consider performance—some databases optimize CTEs better than subqueries, while others materialize CTEs, making them slower.

Understanding these differences will help you write cleaner, more efficient SQL. Try replacing a subquery in your next query with a CTE and see if it improves clarity and performance!