Lesson 9 • Intermediate
Subqueries
Nest queries inside other queries to solve complex data problems that a single query can't handle.
🎯 What You'll Learn
- Subqueries in WHERE with comparison operators
- Correlated vs. non-correlated subqueries
- IN, NOT IN, EXISTS, NOT EXISTS patterns
- Scalar subqueries in SELECT
- Derived tables and CTEs as alternatives
What Is a Subquery?
A subquery is a query nested inside another query. The inner query runs first, and its result is used by the outer query. You can place subqueries in WHERE, SELECT, FROM, and even HAVING clauses.
🎁 Real-World Analogy
A subquery is like asking a follow-up question. "Show me all products" is simple. "Show me all products that cost more than the average price" requires first calculating the average (inner query), then filtering (outer query).
| Type | Returns | Used In |
|---|---|---|
| Scalar | Single value | SELECT, WHERE |
| Row | Single row | WHERE |
| Table | Multiple rows/columns | FROM, IN |
| Correlated | Depends on outer query | WHERE, SELECT |
Subqueries in WHERE
Filter rows using the result of another query
-- Subquery in WHERE: find above-average products
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- Correlated subquery: employees earning more
-- than their department average
SELECT name, department, salary
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
);IN, NOT IN, EXISTS
When a subquery returns multiple values, use IN to check membership or EXISTS to check if any rows match.
⚠️ Common Mistake
NOT IN returns no results if the subquery contains any NULL values! Use NOT EXISTS instead for safety — it handles NULLs correctly.
💡 Pro Tip
EXISTS is usually faster than IN for large datasets because it stops as soon as it finds the first match, while IN must build the entire list first.
IN, NOT IN & EXISTS
Check membership and existence with subqueries
-- IN: customers who have placed orders
SELECT name, email
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id FROM orders
);
-- NOT IN: products never ordered
SELECT product_name, price
FROM products
WHERE product_id NOT IN (
SELECT DISTINCT product_id FROM order_items
);
-- EXISTS: more efficient for large datasets
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);Scalar Subqueries in SELECT
Add computed context to each row using subqueries
-- Scalar subquery in SELECT: add context
SELECT
name,
salary,
(SELECT AVG(salary) FROM employees) AS company_avg,
salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees
ORDER BY diff DESC;
-- Count per row using correlated subquery
SELECT
c.name,
(SELECT COUNT(*) FROM orders o
WHERE o.customer_id = c.customer_id) AS order_count,
(SELECT MAX(total) FROM orders o
WHERE o.customer_id = c.customer_id) AS biggest_order
FROM customers c
ORDER BY
...Derived Tables & CTEs
A subquery in the FROM clause creates a derived table — a temporary result set you can query like a regular table. CTEs (Common Table Expressions) offer cleaner syntax for the same pattern.
💡 Pro Tip
CTEs are easier to read and maintain than nested subqueries. If your subquery is more than a few lines, refactor it into a CTE with WITH ... AS.
Derived Tables & CTEs
Use subqueries in FROM and cleaner CTE alternatives
-- Derived table (subquery in FROM)
SELECT category, avg_price
FROM (
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
) AS category_stats
WHERE avg_price > 50
ORDER BY avg_price DESC;
-- CTE alternative (cleaner syntax)
WITH category_stats AS (
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
)
SELECT category, avg_price
FROM category_stats
WHERE avg_price > 50
ORDER BY avg_price DESC;Advanced Subquery Patterns
ANY, ALL, and top-N per group patterns
-- Real-world: find top 3 customers per region
-- using correlated subquery
SELECT region, customer_name, total_spent
FROM customer_summary cs
WHERE (
SELECT COUNT(*)
FROM customer_summary cs2
WHERE cs2.region = cs.region
AND cs2.total_spent > cs.total_spent
) < 3
ORDER BY region, total_spent DESC;
-- ANY/ALL operators
-- Salary greater than ANY manager's salary
SELECT name, salary
FROM employees
WHERE salary > ANY (
SELECT salary FROM employees WHERE job_title = 'Manager'
...📘 Quick Reference
| Pattern | Use |
|---|---|
| WHERE col > (SELECT ...) | Compare to a single value |
| WHERE col IN (SELECT ...) | Check membership in a list |
| WHERE EXISTS (SELECT ...) | Check if rows exist |
| SELECT (SELECT ...) AS col | Scalar subquery as column |
| WITH cte AS (SELECT ...) | Named reusable subquery |
🎉 Lesson Complete!
You can now nest queries to solve complex data problems. Next, you'll learn to modify data with INSERT, UPDATE, and DELETE!
Sign up for free to track which lessons you've completed and get learning reminders.