Module 18 — Subqueries

1. Scalar Subquery — Returns One Value

What it is: A scalar subquery returns exactly one row and one column — a single value. It can be used in SELECT (as a computed column) or in WHERE (as a comparison value).

Why we use it: When you need to compare each row against a summary value — "each product's price vs the average", "each user's order count vs the total". The subquery computes the summary once and the outer query uses it.

When we use it: When adding computed columns that reference aggregate values, or when filtering based on a calculated threshold.

-- Show each product's price vs the average price
SELECT
    name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products;
 
-- Find products priced above average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

2. IN Subquery — Returns Multiple Values

What it is: The IN operator with a subquery checks if a column's value matches any value returned by the subquery. It's like IN (list) but the list comes from another query.

Why we use it: When the list of values to match against is dynamic — "users who have placed orders", "products that have been reviewed", "courses with enrollments". The subquery generates the list.

When we use it: When filtering based on related data in another table, when building "exists in" conditions, or when the filter list comes from a query.

-- Find users who have placed orders
SELECT username
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
 
-- Find users who have NOT placed orders
SELECT username
FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

3. EXISTS — Check if Rows Exist

What it is: EXISTS checks if a subquery returns any rows. It returns true if the subquery has at least one result, false otherwise. NOT EXISTS is the opposite — true if the subquery returns no rows.

Why we use it: EXISTS is often faster than IN for large tables because it stops scanning as soon as it finds a match (short-circuit evaluation). It's also safer with NULL values.

When we use it: When checking for existence — "users who have orders", "products without reviews", "posts with comments". It's the preferred approach for existence checks.

-- Find users who have placed orders (more efficient than IN for large tables)
SELECT username
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);
 
-- Find users who have NOT placed orders
SELECT username
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 

4. Correlated Subquery — References the Outer Query

What it is: A correlated subquery references columns from the outer query, creating a dependency. It runs once for each row in the outer query — the inner query's result depends on the current row.

Why we use it: When you need per-group comparisons — "the most expensive product in each category", "users whose latest order was over $100", "posts with above-average comments for their author".

When we use it: When finding rows that are extreme within their group, when comparing each row against its group's aggregate, or when the filter condition varies per row.

-- Find the most expensive product in each category
SELECT name, category, price
FROM products p1
WHERE price = (
    SELECT MAX(price)
    FROM products p2
    WHERE p2.category = p1.category
);

5. Subquery in FROM — Derived Table

What it is: A subquery in the FROM clause acts as a temporary table (also called a derived table or inline view). The outer query treats the subquery's result as if it were a regular table.

Why we use it: When you need to filter or aggregate on already-aggregated data — "users whose average order is above $100", "categories with more than 5 expensive products". The inner query computes the first level of aggregation, the outer query filters on it.

When we use it: When you need multi-level aggregation, when you want to simplify complex queries by breaking them into steps, or when you need to join against a computed dataset.

-- Average order total per user
SELECT user_id, avg_order
FROM (
    SELECT user_id, AVG(total) AS avg_order
    FROM orders
    GROUP BY user_id
) AS user_averages
WHERE avg_order > 100;

Subquery vs JOIN

Common Mistakes to Avoid

1. Subquery returns multiple rows when you expect one — Use LIMIT 1 or an aggregate
2. NOT IN with NULLs — Use NOT EXISTS instead
3. Correlated subquery performance — They run once per row, which can be slow

Practice Exercise

Using your courses and students tables:

1. Write a scalar subquery to show each course's price vs the average price
2. Find courses that have enrollments using IN
3. Find courses with no enrollments using NOT EXISTS
4. Write a correlated subquery to find the most expensive course per instructor
5. Use a subquery in FROM to find instructors with average course price > 3000