SQL Subqueries vs CTEs

#tutorial #beginners #database #sql

Ever written a query… And halfway through forgot what you were doing?

You start simple:
SELECT * FROM books;
Then suddenly you're nesting queries inside queries…
Adding conditions inside conditions…
And now your SQL looks like a maze you can’t escape.

That’s exactly where Subqueries and CTEs (Common Table Expressions) come in.

Let’s break them down.

What is a subquery?
A subquery is just a query inside another query.
Think of it like this:
“Get me results… based on another result.”
It runs first, then feeds its result into the main query.

Example: Find books priced above average
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

What’s happening here:
The inner query calculates the average price
The outer query filters books above that value

*When Should You Use Subqueries?
*
Subqueries are perfect when you want to:

Filter data using another query
Compare values dynamically
Avoid writing multiple separate queries
Keep logic compact

*Types of Subqueries.
*

Scalar Subquery (Single Value)
Returns one value.
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);
Multi-row Subquery
Returns multiple values.
Show me books ordered by customers named John
SELECT title, author
FROM books
WHERE book_id IN (
SELECT book_id
FROM orders
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE first_name = 'John'));
Correlated Subquery (The “Smart” One)
This one depends on the outer query.
Show me customers who have at least one order.
SELECT first_name, last_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id);
Derived Table (Subquery in FROM)
Returns a full table.
Give me the top 3 most expensive books.
SELECT title, author, price
FROM (
SELECT
title,
author,
price,
RANK() OVER (ORDER BY price DESC) AS price_rank
FROM books
) ranked_books
WHERE price_rank <= 3;

*What Are CTEs?
*
A CTE (Common Table Expression) is like giving your query a temporary name.
Instead of nesting everything, you say:
Let me define this part first… then use it.

Syntax (Simple but powerful)
WITH cte_name AS (
-- your query here)
SELECT * FROM cte_name;

Example: Total orders per customer
WITH customer_order_count AS (
SELECT
customer_id,
COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id)
SELECT
c.first_name,
c.last_name,
co.total_orders
FROM customers c
JOIN customer_order_count co
ON c.customer_id = co.customer_id;

Translation:

First, calculate total orders per customer
Then join it with customer details

Much cleaner than nesting everything.

Recursive CTEs (This is where things get interesting)

Used for:
hierarchies
sequences
tree structures

Example: Generate dates
WITH RECURSIVE date_sequence AS (
SELECT MIN(order_date) AS order_date
FROM orders
UNION ALL
SELECT order_date + INTERVAL '1 day'
FROM date_sequence
WHERE order_date < (
SELECT MIN(order_date) + INTERVAL '5 days'
FROM orders))
SELECT order_date
FROM date_sequence;

Translation:
Start from the first date… Keep adding 1 day… stop after 5 days.

Subqueries vs. CTEs—The Real Difference
| Feature | Subquery | CTE |
| ----------- | --------------------- | ---------------------- |
| Readability | Gets messy fast | Clean and structured |
| Reusability | Not reusable | Reusable in same query |
| Complexity | Good for simple logic | Best for complex logic |
| Debugging | Hard | Easy |
| Recursion | ❌ No | ✅ Yes

So… Which One Should You Use?

Use subqueries when: