Advanced SQL: CTEs & Subqueries

Queries Inside of Queries nesting dolls of SQL

A simple query is like a simple sentence. Subqueries and CTEs are like using clauses and well-defined paragraphs to build a more complex, structured argument. They let you break down a complex problem into smaller, logical, and more manageable steps.

The Power of Subqueries

1. Single-Row Subquery

Used when you need **one specific value** to make a comparison. The subquery must return exactly one row and one column.

Business Question: "Find all movies rated higher than the average rating of all movies."

SELECT title, rating
FROM movies
WHERE rating > (SELECT AVG(rating) FROM movies);

2. Multi-Row Subquery

Used when you need to compare a value against a **list of possible values**. It uses operators like `IN`, `NOT IN`, `ANY`, and `ALL`.

Business Question: "Get the names of all customers who have rented a Sci-Fi movie."

SELECT first_name, last_name
FROM customers
WHERE customer_id IN (
    SELECT rh.customer_id
    FROM rental_history rh
    JOIN media m ON rh.media_id = m.media_id
    JOIN movies mv ON m.title_id = mv.title_id
    WHERE mv.category = 'SCIFI'
);

Cleaning It Up with CTEs (Naming Your Steps)

A Common Table Expression, or CTE, is a temporary, named result set that you can reference within your main query. You define it using the `WITH` clause.

Think of it this way: if a subquery is a nested thought, a CTE is like writing that thought on a sticky note, giving it a name, and then referring to that note in your main sentence. It makes your work dramatically easier to read, debug, and reuse.

Example: Rewriting our subquery with a CTE

Let's solve the same "Sci-Fi renters" problem. Notice how much cleaner this is:

WITH SciFiRenters AS (
    -- This CTE gets a list of customers who rented Sci-Fi movies
    SELECT DISTINCT rh.customer_id
    FROM rental_history rh
    JOIN media me ON rh.media_id = me.media_id
    JOIN movies m ON me.title_id = m.title_id
    WHERE m.category = 'SCIFI'
)
-- Now, we use our named result set in the main query
SELECT c.first_name, c.last_name
FROM customers c
JOIN SciFiRenters sfr ON c.customer_id = sfr.customer_id;

CTE vs. Subquery: When to Use Which?

Use a Subquery When...

The logic is very simple and short.
The result is not needed anywhere else in the query.
A quick `WHERE ID IN (...)` is all you need.

Use a CTE When...

**Readability is critical** for a long, complex query.
You need to **reuse the same result set** multiple times.
You are building up logic step-by-step.
You need to perform recursive queries (like for an org chart).