SQL 43 🛢️ Subqueries in WHERE Clauses - KandZ

A query that contains another query inside it. This is the essential idea behind the subquery, and it is one of the most powerful tools in SQL. A subquery lets you use the result of one query as input to another, without creating temporary tables or writing procedural code. The WHERE clause is the most common place to find subqueries, because filtering rows based on the results of another query is a fundamental analytical need: find customers who have ordered, products that are not in stock, employees who earn more than the department average.

This chapter covers subqueries in WHERE clauses in full. You will learn the three main forms—scalar subqueries, IN subqueries, and EXISTS subqueries—and the specific situations each one handles best. You will see the difference between correlated and uncorrelated subqueries, and why that difference matters for performance. You will also work through the comparison operators that work with subqueries, including the ANY, ALL, and SOME quantifiers.

By the end, you will understand not just how to write a subquery but which form to reach for, how the database executes it, and why some forms are faster than others on large tables.

Key point: A subquery in a WHERE clause is a complete SELECT statement nested inside the filter condition of another query. It can return a single value (scalar), a single column of values (for IN), or a boolean result (for EXISTS). The form you choose determines what the subquery can compare against and how the database executes it.

Why subqueries in WHERE exist

The filtering problem. SQL’s WHERE clause filters rows based on conditions. But some conditions depend on data in other tables or on aggregates computed from other rows. “Find customers who have placed orders” requires checking the orders table for each customer. “Find products priced above the average” requires computing an average first. Subqueries express these conditions directly, without joins that would multiply rows or temporary tables that add complexity.

The join limitation problem. Joins combine rows from multiple tables, but they can produce duplicate rows when the relationship is one-to-many. A customer with three orders appears three times in a join between customers and orders. A subquery with IN or EXISTS avoids this multiplication because it only checks for existence, not for the matching rows themselves. When you want to filter, not combine, subqueries are often the more natural expression.

The aggregation problem. Some filters depend on aggregates—AVG, MAX, SUM—computed over a set of rows. You cannot use an aggregate directly in a WHERE clause because WHERE is evaluated before aggregation. But you can compute the aggregate in a subquery and use its result in the outer WHERE. This pattern is essential for queries like “employees earning more than the company average.”

The readability problem. A query with three nested subqueries can be hard to read. But a query with one well-named subquery is often clearer than the equivalent join, especially when the subquery expresses a distinct concept (“customers who have ordered”) that can be named and understood independently. Subqueries allow the query to be broken into logical pieces.

The correlated subquery problem. A correlated subquery references a column from the outer query. This makes it powerful—it can compute a different result for each row of the outer query—but also potentially slow, because the subquery may execute once per outer row. Understanding when a subquery is correlated and how the database handles it is essential for writing performant queries.

a. Scalar subqueries

A scalar subquery returns exactly one row and one column—a single value. It can be used anywhere a single value is expected: in comparisons, in arithmetic, in SELECT lists. The most common use is comparing a column to an aggregate computed from another table.

-- Find employees earning more than the average salary
SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The subquery (SELECT AVG(salary) FROM employees) returns a single number. The outer query compares each employee’s salary to that number. The subquery is uncorrelated—it does not reference the outer query—so the database computes it once and reuses the result for every row.

A scalar subquery that returns zero rows produces NULL instead of a value. A scalar subquery that returns more than one row produces an error. These behaviors matter when the subquery’s result is not guaranteed to be unique.

-- This fails if more than one employee has the max salary
SELECT employee_id, name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

If two employees tie for the maximum salary, the subquery returns two rows, and the query fails. The fix is to use IN instead of =.

SELECT employee_id, name
FROM employees
WHERE salary IN (SELECT MAX(salary) FROM employees);

The IN form handles multiple rows correctly.

b. IN and NOT IN subqueries

An IN subquery returns a single column of values, and the outer query checks whether a column’s value matches any of them. This is the most common subquery form.

-- Find customers who have placed orders
SELECT customer_id, name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

The subquery returns the list of customer IDs that appear in the orders table. The outer query returns customers whose ID is in that list. This is an uncorrelated subquery—it does not depend on the outer query—and the database typically computes it once.

The NOT IN variant finds rows that do not match any value in the subquery.

-- Find customers who have not placed orders
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

This produces the desired result, but only if the subquery’s result contains no NULL values. If orders.customer_id contains a single NULL, the entire NOT IN predicate evaluates to NULL for every row, and the query returns nothing. This is the null trap discussed in the previous chapter. The safe alternatives are NOT EXISTS or EXCEPT.

c. EXISTS and correlated subqueries

The EXISTS operator checks whether a subquery returns any rows at all. It does not care about the values, only about whether rows exist. This makes it ideal for existence checks, and it is often the fastest form because the database can stop scanning as soon as it finds a match.

-- Find customers who have placed orders
SELECT customer_id, name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

This subquery is correlated: it references c.customer_id from the outer query. For each customer, the database runs the subquery to check whether any order exists for that customer. The subquery stops as soon as it finds one matching row, so the cost is proportional to the number of customers, not the number of orders.

The SELECT 1 is a convention. The actual columns selected do not matter because EXISTS only checks for row presence. Some databases optimize SELECT 1 slightly better than SELECT *, but the difference is negligible.

The NOT EXISTS form finds rows with no matching subquery result.

-- Find customers who have not placed orders
SELECT customer_id, name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

Unlike NOT IN, NOT EXISTS handles nulls correctly. If orders.customer_id contains NULL, the correlated comparison o.customer_id = c.customer_id evaluates to NULL for that row, and EXISTS treats it as not a match. The outer query correctly returns customers with no matching orders.

Complete Example Session

-- ============================================
-- PART 1: SAMPLE DATA
-- ============================================
CREATE TABLE employees (
  employee_id INT,
  name VARCHAR(50),
  department VARCHAR(20),
  salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
  (1, 'Alice', 'Engineering', 95000),
  (2, 'Bob', 'Engineering', 85000),
  (3, 'Carol', 'Sales', 75000),
  (4, 'Dave', 'Sales', 65000),
  (5, 'Eve', 'Marketing', 70000);

-- ============================================
-- PART 2: SCALAR SUBQUERY WITH AGGREGATE
-- ============================================
SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Result: Alice (95000), Bob (85000)

-- ============================================
-- PART 3: SCALAR SUBQUERY PER DEPARTMENT
-- ============================================
SELECT employee_id, name, department, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees
  WHERE department = e.department
);
-- Correlated: computes average per department

-- ============================================
-- PART 4: IN SUBQUERY
-- ============================================
CREATE TABLE departments (name VARCHAR(20));
INSERT INTO departments VALUES ('Engineering'), ('Sales');

SELECT employee_id, name, department
FROM employees
WHERE department IN (SELECT name FROM departments);

-- ============================================
-- PART 5: NOT IN SUBQUERY
-- ============================================
SELECT employee_id, name, department
FROM employees
WHERE department NOT IN (SELECT name FROM departments);
-- Result: Eve (Marketing)

-- ============================================
-- PART 6: THE NULL TRAP
-- ============================================
INSERT INTO departments VALUES (NULL);

SELECT employee_id, name, department
FROM employees
WHERE department NOT IN (SELECT name FROM departments);
-- Result: empty! The NULL makes all comparisons NULL

-- ============================================
-- PART 7: NOT EXISTS IS NULL-SAFE
-- ============================================
SELECT employee_id, name, department
FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM departments d
  WHERE d.name = e.department
);
-- Result: Eve (Marketing) — correct despite NULL in departments

-- ============================================
-- PART 8: EXISTS WITH CORRELATION
-- ============================================
SELECT employee_id, name
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees e2
  WHERE e2.department = e.department
    AND e2.salary > e.salary
);
-- Employees who are not the highest paid in their department

-- ============================================
-- PART 9: SUBQUERY WITH ANY AND ALL
-- ============================================
-- Employees earning more than at least one Sales employee
SELECT employee_id, name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department = 'Sales'
);

-- Employees earning more than every Sales employee
SELECT employee_id, name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department = 'Sales'
);

-- ============================================
-- PART 10: NESTED SUBQUERIES
-- ============================================
SELECT employee_id, name
FROM employees
WHERE department IN (
  SELECT name FROM departments
  WHERE name IN (
    SELECT DISTINCT department FROM employees
    WHERE salary > 80000
  )
);
-- Employees in departments that have at least one employee earning > 80000

The ten parts covered the full range of subquery usage in WHERE clauses: scalar subqueries with aggregates, correlated scalar subqueries, IN, NOT IN and its null trap, NOT EXISTS as the null-safe alternative, EXISTS with correlation, the ANY and ALL quantifiers, and nested subqueries.

Quick Reference

Subquery Forms in WHERE

Form	Returns	Use Case
Scalar	One value	Comparison with aggregate
`IN`	One column, many rows	Membership check
`NOT IN`	One column, many rows	Exclusion (null-unsafe)
`EXISTS`	Boolean	Existence check
`NOT EXISTS`	Boolean	Absence check (null-safe)
`ANY` / `SOME`	Boolean	Comparison with at least one
`ALL`	Boolean	Comparison with all

Correlated vs Uncorrelated

Aspect	Uncorrelated	Correlated
References outer query	No	Yes
Executes	Once	Once per outer row
Performance	Usually faster	Potentially slower
Example	`IN (SELECT ...)`	`EXISTS (SELECT ... WHERE x = outer.x)`

Comparison Operators with Subqueries

Operator	Subquery Returns	Example
`=`	Single value	`salary = (SELECT MAX(...))`
`>` `<` `>=` `<=`	Single value	`salary > (SELECT AVG(...))`
`IN`	Multiple values	`id IN (SELECT ...)`
`ANY`	Multiple values	`salary > ANY (SELECT ...)`
`ALL`	Multiple values	`salary > ALL (SELECT ...)`
`EXISTS`	Any rows	`EXISTS (SELECT 1 ...)`

Null Safety

Operator	Null-Safe	Notes
`IN`	Yes	Nulls in subquery are ignored
`NOT IN`	No	Null in subquery returns no rows
`EXISTS`	Yes	Correlated comparison handles nulls
`NOT EXISTS`	Yes	Safe alternative to `NOT IN`

Best Practices

✅ Do This:

-- Use EXISTS for existence checks
SELECT c.id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.id);                  -- ✅

-- Use NOT EXISTS instead of NOT IN
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.id);              -- ✅

-- Use scalar subquery for aggregate comparison
SELECT id, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);         -- ✅

-- Use IN for static lists from subqueries
SELECT id FROM customers
WHERE region_id IN (SELECT id FROM regions
                    WHERE active = true);                    -- ✅

-- Use ALL for "more than every"
SELECT id FROM employees
WHERE salary > ALL (SELECT salary FROM interns);            -- ✅

❌ Don’t Do This:

-- Use NOT IN with nullable subquery
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);           -- ❌ if orders has NULL

-- Use = with a subquery that may return multiple rows
SELECT id FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);         -- ❌ if tie

-- Use SELECT * in EXISTS
SELECT c.id FROM customers c
WHERE EXISTS (SELECT * FROM orders o
              WHERE o.customer_id = c.id);                  -- ❌ (SELECT 1 preferred)

-- Nest deeply without need
SELECT id FROM a
WHERE id IN (SELECT id FROM b
  WHERE id IN (SELECT id FROM c
    WHERE id IN (SELECT id FROM d)));                       -- ❌ (use joins)

Common Pitfalls

Pitfall	Why It Happens	Fix
`NOT IN` returns no rows	Subquery contains `NULL`	Use `NOT EXISTS`
`=` fails with multiple rows	Subquery returns more than one value	Use `IN` or `ANY`
Scalar subquery returns `NULL`	Subquery found no rows	Use `COALESCE` or `EXISTS`
Slow correlated subquery	No index on correlation column	Add index or rewrite as join
`EXISTS` always true	Subquery missing correlation	Add `WHERE` linking to outer query
Wrong `ANY` / `ALL` semantics	Misunderstanding quantifiers	`> ANY` means greater than at least one

Real-World Examples

1. Customers Who Ordered

SELECT customer_id FROM customers
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = customers.customer_id);

2. Customers Who Did Not Order

SELECT customer_id FROM customers
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = customers.customer_id);

3. Employees Above Average

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

4. Employees Above Department Average

SELECT name, department FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees
                WHERE department = e.department);

5. Products Not in Inventory

SELECT product_id FROM products
WHERE product_id NOT IN (SELECT product_id FROM inventory);

6. Products Not in Inventory (Null-Safe)

SELECT product_id FROM products p
WHERE NOT EXISTS (SELECT 1 FROM inventory i
                  WHERE i.product_id = p.product_id);

7. Highest Paid per Department

SELECT name, department FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees
                WHERE department = e.department);

8. Employees Earning More Than All Interns

SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM interns);

9. Users With No Recent Login

SELECT user_id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM logins l
  WHERE l.user_id = u.user_id
    AND l.login_date > CURRENT_DATE - INTERVAL '30 days'
);

10. Orders with High-Value Items

SELECT order_id FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.order_id = o.order_id
    AND oi.price > 1000
);

Visual

Subquery Types in WHERE

┌─────────────────────────────────────────────────────────────┐
│  THREE SUBQUERY FORMS IN WHERE                              │
│                                                             │
│  1. SCALAR                                                  │
│     WHERE salary > (SELECT AVG(salary) FROM employees)      │
│     └── Returns one value, used in comparison               │
│                                                             │
│  2. IN                                                      │
│     WHERE id IN (SELECT customer_id FROM orders)            │
│     └── Returns a list, checks membership                   │
│                                                             │
│  3. EXISTS                                                  │
│     WHERE EXISTS (SELECT 1 FROM orders                      │
│                   WHERE customer_id = customers.id)         │
│     └── Returns boolean, checks existence                   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Correlated vs Uncorrelated Execution

┌─────────────────────────────────────────────────────────────┐
│  UNCORRELATED: SUBQUERY RUNS ONCE                           │
│                                                             │
│  SELECT id FROM customers                                   │
│  WHERE id IN (SELECT customer_id FROM orders);              │
│                                                             │
│  1. Execute subquery → [1, 3, 5, 7]                         │
│  2. Scan customers, filter where id IN [1, 3, 5, 7]         │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  CORRELATED: SUBQUERY RUNS PER OUTER ROW                    │
│                                                             │
│  SELECT id FROM customers c                                 │
│  WHERE EXISTS (SELECT 1 FROM orders o                       │
│                WHERE o.customer_id = c.id);                 │
│                                                             │
│  For each customer row:                                     │
│    1. Execute subquery with c.id                            │
│    2. Check if any row returned                             │
│                                                             │
│  Index on orders.customer_id makes this efficient.          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

NOT IN vs NOT EXISTS with NULL

┌─────────────────────────────────────────────────────────────┐
│  NOT IN WITH NULL                                           │
│                                                             │
│  customers: [1, 2, 3]                                       │
│  orders.customer_id: [1, NULL]                              │
│                                                             │
│  WHERE id NOT IN (SELECT customer_id FROM orders)           │
│                                                             │
│  For id = 2:                                                │
│    2 <> 1    → TRUE                                         │
│    2 <> NULL → NULL                                         │
│    TRUE AND NULL = NULL                                     │
│  → id = 2 not returned                                      │
│                                                             │
│  Result: empty                                              │
│                                                             │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  NOT EXISTS WITH NULL                                       │
│                                                             │
│  WHERE NOT EXISTS (SELECT 1 FROM orders o                   │
│                    WHERE o.customer_id = c.id)              │
│                                                             │
│  For id = 2:                                                │
│    Subquery: SELECT 1 FROM orders WHERE customer_id = 2     │
│    → no rows (NULL does not equal 2)                        │
│    → NOT EXISTS is TRUE                                     │
│  → id = 2 returned                                          │
│                                                             │
│  Result: [2, 3]                                             │
│                                                             │
└─────────────────────────────────────────────────────────────┘

ANY and ALL Semantics

┌─────────────────────────────────────────────────────────────┐
│  COMPARISON QUANTIFIERS                                     │
│                                                             │
│  > ANY (SELECT salary FROM sales)                           │
│  → Greater than at least one salary in sales                │
│  → Equivalent to > MIN(salary)                              │
│                                                             │
│  > ALL (SELECT salary FROM sales)                           │
│  → Greater than every salary in sales                       │
│  → Equivalent to > MAX(salary)                              │
│                                                             │
│  = ANY (SELECT id FROM active_users)                        │
│  → Equivalent to IN                                         │
│                                                             │
│  <> ALL (SELECT id FROM banned_users)                       │
│  → Equivalent to NOT IN                                     │
│                                                             │
└─────────────────────────────────────────────────────────────┘

Summary

Item	Value
Scalar subquery	Returns one value, used in comparison
`IN` subquery	Returns a list, checks membership
`NOT IN`	Exclusion, null-unsafe
`EXISTS`	Boolean existence check
`NOT EXISTS`	Boolean absence check, null-safe
Correlated subquery	References outer query, runs per row
Uncorrelated subquery	Independent, runs once
`ANY` / `SOME`	True if comparison holds for at least one
`ALL`	True if comparison holds for all
Null trap	`NOT IN` with `NULL` returns no rows
Safe alternative	`NOT EXISTS` handles nulls correctly

Key takeaways:

Scalar subqueries return one value. They work with comparison operators like =, >, and <. If they return more than one row, the query fails. If they return no rows, the result is NULL.
IN subqueries return a list. They check whether a column’s value appears in that list. This is the most common subquery form for filtering.
NOT IN has a null trap. If the subquery returns any NULL, the entire predicate evaluates to NULL, and no rows are returned. This is a common and dangerous bug.
EXISTS checks for row presence. It returns true if the subquery returns any rows, false otherwise. The columns selected in the subquery do not matter; SELECT 1 is a common convention.
NOT EXISTS is null-safe. It correctly handles cases where the subquery’s comparison column contains NULL. It is the preferred alternative to NOT IN.
Correlated subqueries run per outer row. They reference a column from the outer query and can be slow without proper indexing. Uncorrelated subqueries run once and are generally faster.
ANY and ALL quantify comparisons. > ANY means greater than at least one value; > ALL means greater than every value. These are equivalent to comparisons against MIN and MAX in many cases.
Choose the form that matches the intent. Use EXISTS for existence checks, IN for membership, and scalar subqueries for aggregate comparisons. The right choice is usually the one that reads most directly.

Remember: Subqueries in WHERE clauses let you express complex filters without joins or temporary tables. But they come in several forms, and the choice among them affects both correctness and performance. The NOT IN trap is the most important pitfall to internalize: any subquery that can return NULL should be paired with NOT EXISTS instead. On large tables, EXISTS with an index on the correlation column is often the fastest form. Write the query that expresses the logic clearly, then test its performance, then adjust if needed. Clarity first, correctness always, performance when it matters.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!