Correlated Subqueries and EXISTS

A correlated subquery uses a value from the outer query, so it runs once for every outer row. That lets you compare each row with its own group, like each product against the average of its category. The EXISTS operator builds on this to test whether related rows exist.

Syntax

-- Correlated subquery
SELECT ...
FROM table_a a
WHERE a.col > (SELECT AVG(b.col) FROM table_b b WHERE b.key = a.key);

-- EXISTS
SELECT ...
FROM table_a a
WHERE EXISTS (SELECT 1 FROM table_b b WHERE b.key = a.key);

Correlated subqueries

1. Products priced above their category average

SELECT p.product_name,
       p.category_id,
       p.price
FROM products p
WHERE p.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category_id = p.category_id   -- linked to the outer row
)
ORDER BY p.category_id;

Result:

(7 rows)

For the Redmi phone, the subquery averages Electronics prices only; for the saree, it averages Fashion prices. That's the "correlation".

2. Correlated subquery in SELECT

Each customer's most recent order date:

SELECT c.customer_name,
       (SELECT MAX(o.order_date)
        FROM orders o
        WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c
ORDER BY last_order_date DESC NULLS LAST
LIMIT 5;

Result:

(5 rows)

3. Each customer's latest order

Combine both ideas: keep only orders whose date equals that customer's latest date.

SELECT o.customer_id, o.order_id, o.order_date, o.status
FROM orders o
WHERE o.order_date = (
  SELECT MAX(o2.order_date)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id
)
ORDER BY o.customer_id;

Result:

(11 rows)

Window functions solve this more efficiently on large tables, but the logic here is easier to read when you're starting out.

EXISTS and NOT EXISTS

EXISTS (subquery) is true if the subquery returns any row. The columns it selects don't matter, so people write SELECT 1.

4. Customers who have placed at least one order

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_name
LIMIT 5;

Result:

(5 rows)

5. Customers with a cancelled or returned order

SELECT c.customer_name, c.city
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status IN ('Cancelled', 'Returned')
);

Result:

(4 rows)

6. NOT EXISTS: products never ordered

SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

Result:

(1 row)

Why NOT EXISTS beats NOT IN

NOT IN breaks when the subquery contains a NULL. Watch what happens if we look for products whose brand is not used by any Electronics product, and the subquery includes a NULL brand:

SELECT COUNT(*) AS not_in_result
FROM products
WHERE brand NOT IN (SELECT brand FROM products WHERE category_id IN (1, 6));

Result:

(1 row)

Zero, because the cricket bat's NULL brand makes every NOT IN comparison unknown. NOT EXISTS gives the right answer:

SELECT COUNT(*) AS not_exists_result
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM products p2
  WHERE p2.category_id IN (1, 6) AND p2.brand = p.brand
);

Result:

(1 row)

(It also counts the cricket bat itself, since a NULL brand matches nothing.) Default to NOT EXISTS for "not in another table" checks.

Common mistakes

• Forgetting the correlation. Without WHERE o.customer_id = c.customer_id, EXISTS checks whether the orders table has any row, and returns every customer.
• Using a correlated subquery on millions of rows when a join to a grouped table or a window function would run once.

Practice question

Find customers who have bought from more than one category. (Hint: a correlated subquery that counts distinct categories per customer.)

SELECT c.customer_name
FROM customers c
WHERE (
  SELECT COUNT(DISTINCT p.category_id)
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  JOIN products p     ON p.product_id = oi.product_id
  WHERE o.customer_id = c.customer_id
) > 1
ORDER BY c.customer_name
LIMIT 6;

Key takeaways

• A correlated subquery refers to the outer row and runs per row.
• It's ideal for "compare to my own group" questions.
• EXISTS tests whether matching rows exist; NOT EXISTS tests that none do.
• Prefer NOT EXISTS over NOT IN when NULLs are possible.