Understanding Subqueries

When One Query Isn’t Enough

Here’s a question that comes up all the time: “Find everyone who earns more than average.” Sounds simple, but think about it. You need to know the average before you can filter by it. That’s two questions, not one.

You could run two queries. First, get the average, write it down, then use that number in your WHERE clause. But that’s tedious, error-prone, and falls apart when the data changes. Subqueries let you do both in one shot.

A subquery is just a query inside another query. The inner one runs first, and its result gets plugged into the outer one. This module teaches you both subqueries and CTEs (Common Table Expressions). We’ll start with subqueries because you’ll encounter them constantly in existing code, then move to CTEs, which are StrataScratch’s recommended approach for new queries.

Subqueries in WHERE Clause

Your First Subquery

SELECT *
FROM orders
WHERE total_order_cost > (
  SELECT AVG(total_order_cost) FROM orders
);

The inner query calculates the average salary. The outer query uses it to filter. No hardcoded numbers, no two-step process.

The Classic “Find the Max” Pattern

This comes up constantly in interviews: find the row with the highest value for a given column.

SELECT
  first_name,
  last_name,
  salary
FROM employees
WHERE salary = (
  SELECT MAX(salary)
  FROM employees
);

The subquery finds the highest salary. The outer query finds everyone earning that amount. If there’s a tie, you get multiple rows.

Workers With The Highest Salaries

Combine subqueries with JOINs to find top performers.

IN: Matching a List

What if your subquery returns multiple rows? That’s when you use IN.

-- Find employees in departments that have high earners
SELECT
  first_name,
  last_name,
  department
FROM employees
WHERE department IN (
  SELECT DISTINCT department
  FROM employees
  WHERE salary > 100000
);

NOT IN: The Trap Everyone Falls Into

NOT IN seems like the obvious way to exclude values. And it works great, until it doesn’t.

Here’s the problem: if that subquery returns even one NULL value, NOT IN returns zero rows. Not some rows. Zero. It’s bitten every SQL developer at least once.

EXISTS and NOT EXISTS

EXISTS asks a simple question: “Does at least one matching row exist?” It returns TRUE or FALSE, and stops searching the moment it finds a match.

-- Find customers who have placed at least one order
SELECT
  c.customer_id,
  c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.cust_id = c.customer_id
);

That SELECT 1 isn’t magic. EXISTS only cares whether rows exist, not what’s in them.

NOT EXISTS: The NULL-Safe Alternative

NOT EXISTS finds rows where no matching rows exist. It’s cleaner and safer than NOT IN.

-- Find customers who have never ordered
SELECT
  c.customer_id,
  c.customer_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.cust_id = c.customer_id
);

Customers with Large Orders

Use EXISTS with a correlated subquery that checks for orders over $100.

Subqueries in SELECT

You can put a subquery in SELECT to add a calculated column to every row.

This counts orders for each customer. The subquery runs once per row. For small tables, that’s fine. For big tables, it can get slow.

Derived Tables: Subqueries in FROM

Put a subquery in FROM to create a temporary result set, then query it like a regular table.

SELECT
  department,
  total_salary
FROM (
  SELECT
    department,
    SUM(salary) AS total_salary
  FROM employees
  GROUP BY department
) AS dept_totals
WHERE total_salary > 200000;

The inner query aggregates. The outer query filters those aggregates.

Correlated Subqueries

The subqueries above can run independently. Correlated subqueries are different: they reference the outer query and run once per row.

That e1.department reference is what makes it correlated. The subquery needs to know which row we’re on.

The Nesting Problem

Subqueries can nest inside each other. And nest again. Technically, SQL allows arbitrary nesting depth. Practically, two levels deep and you’re already in trouble.

-- Don’t do this
SELECT *
FROM (
  SELECT *
  FROM (
    SELECT
      department,
      AVG(salary) AS avg
    FROM employees
    GROUP BY department
  ) AS level1
  WHERE avg > 50000
) AS level2
WHERE avg < 500000;

This is hard to read, hard to debug, and makes your colleagues hate you. If you ever find yourself nesting more than one level deep, that’s your signal to switch to CTEs.

Why CTEs Are Preferred

CTEs (Common Table Expressions) solve the nesting problem. They let you name each step and lay them out top-to-bottom instead of inside-out.

Benefits of CTEs over nested subqueries:

• Readable: you can follow the logic from top to bottom
• Debuggable: test each step independently by running just that CTE
• Reusable: reference the same result multiple times without repeating code
• Maintainable: easier to modify six months later

The rest of this module focuses on CTEs because they’re usually the recommended approach. You now understand subqueries well enough to read legacy code and recognize when CTEs would be cleaner.

SELECT
  c.id,
  c.first_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.cust_id = c.id
);

Key Takeaways

• Subqueries let you use the result of one query inside another
• Scalar subqueries return one value; use with =, >, <
• IN handles subqueries that return multiple rows
• NOT IN fails silently with NULLs; use NOT EXISTS for safety
• EXISTS stops at the first match and is NULL-safe
• Derived tables (FROM subqueries) must have an alias
• Deep nesting is a code smell; CTEs are the cleaner solution

What’s Next

Now that you understand subqueries, you’re ready for CTEs. They use the WITH clause to name each step, making complex queries readable and maintainable. Once you start using them, you’ll wonder how you ever lived without them.