Young Odhiambo

Posted on Sep 8

CTE vs. Subquery: The Ultimate Battle for Your Database's Sanity

#database #datascience #data #beginners

Introduction

Ever opened a SQL file and felt like you needed a map just to find where the actual query starts? Nested logic is one of the quickest ways to turn clean database code into an unreadable mess.

When you need intermediate calculations, two heavy hitters usually step into the ring: Subqueries and Common Table Expressions (CTEs). While both solve the problem of querying temporary sets of data, they approach it from completely different angles—one by nesting logic inside your clauses, and the other by breaking it out into neat, named steps.

In this guide, we’ll break down what each tool does best, highlight their key differences, and walk through practical examples so you know exactly when to nest and when to use WITH.

What Is a Subquery?

A subquery is a query nested inside another query. The inner query runs first, its result is used by the outer query. Think of it as using the answer to one question as the input to another question.
Subqueries show up in one of three places:

In a WHERE clause, filters rows based on a computed value:

-- Find all orders above the average order value
SELECT order_id, amount
FROM orders
WHERE amount > (
  SELECT AVG(amount) FROM orders
);

The inner query SELECT AVG(amount) FROM orders runs first and returns a single number (say, 450). The outer query then uses that number: WHERE amount > 450

In a FROM clause, acting as a temporary, unnamed table:

-- Find cities where average order value > 500
SELECT city, avg_value
FROM (
  SELECT city, AVG(amount) AS avg_value
  FROM orders
  GROUP BY city
) AS city_stats
WHERE avg_value > 500;

The subquery in FROM creates a temporary table (city_stats) that the outer query treats like a regular table.

In Select clause, Used to compute additional values for each row.

SELECT 
    e1.student_id,
    e1.marks,
    (
        SELECT AVG(e2.marks)
        FROM exam_results e2
        WHERE e2.student_id = e1.student_id
    ) AS student_avg
FROM exam_results e1;

Types of Subqueries

1. Single Row Subqueries:

They return a single row and a single column.
Typically used with comparison operators like =,<,>,…

SELECT *
FROM Clients
WHERE revenue > (SELECT AVG(revenue) FROM Clients);

2. Multiple Row Subqueries:

Return multiple rows and a single column.
They are used with operators like IN, ANY, ALL.

SELECT *
FROM Clients
WHERE country IN (SELECT country FROM Clients GROUP BY country HAVING COUNT(*) > 5);

3. Multiple Column Subqueries

Return multiple columns.
Often used in the FROM clause or with IN or EXISTS.

SELECT client_id, name
FROM Clients
WHERE (country, sales_rep_id) IN
(SELECT country, sales_rep_id FROM Clients WHERE client_id = 1);

4. Correlated Subqueries

A subquery that references columns from the outer query.
They are re-evaluated for each row processed by the outer query.

SELECT c1.client_id, c1.name, c1.revenue
FROM Clients c1
WHERE c1.revenue > (SELECT AVG(c2.revenue) FROM Clients c2 WHERE c2.country = c1.country);

What is a CTE?

A CTE(Common Table Expression) is a temporary named result set defined at the top of your query using WITH, and referenced later as a table for the rest of your query.
It does the same job as a subquery but is written before the main query — making complex logic much easier to read and debug.

Example of a CTE

-- Same query as above, rewritten as a CTE
WITH city_stats AS (
  SELECT
    city,
    AVG(amount) AS avg_value,
    COUNT(*) AS order_count
  FROM orders
  GROUP BY city
)
SELECT city, avg_value, order_count
FROM city_stats
WHERE avg_value > 500
ORDER BY avg_value DESC;

Chaining Multiple CTEs

You can define multiple CTEs and reference earlier ones in later ones:

WITH
monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
  FROM monthly_revenue
)
SELECT *
FROM revenue_with_growth
WHERE month >= '2026-01-01';

Conclusion

In conclusion, both Common Table Expressions (CTEs) and subqueries are invaluable tools in SQL. CTEs provide a structured and readable way to handle complex queries and recursive data structures, making them essential for advanced SQL operations. Subqueries, on the other hand, offer a flexible and often performance-efficient method for embedding queries within queries. Understanding when and how to use these features can significantly enhance your SQL capabilities.

DEV Community