Subqueries: IN, EXISTS, or a join?

A subquery is a query in brackets inside another query. The inner query produces a value or a list, and the outer query uses it. That lets you ask about one table using facts that live in another, without joining the two.

SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id FROM orders WHERE status = 'refunded'
);

-- Maja Berg
-- Aisha Khan

A subquery that returns a single value can go anywhere a number can. The average list price is 76.125, so this returns the three products priced above it: Trail Runner Shoes, Rain Shell Jacket and Trekking Poles. When prices change, the query still works, because nothing is hard-coded.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

EXISTS asks whether a matching row exists at all. Its subquery can refer to the row the outer query is looking at, here c.id, so it runs once per customer. NOT EXISTS finds customers with no orders: the same Omar Haddad the LEFT JOIN found last lesson.

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

-- Omar Haddad

Be careful with NOT IN. x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL, and a comparison with NULL is never true. So if the subquery's list contains a single NULL, NOT IN matches nothing, with no error. NOT EXISTS doesn't have this problem, which is why it's the safer habit.

Subquery or join? Use a join when you need columns from the other table. Use IN or EXISTS when you only need to know whether a match exists. A join returns a row for every match, so a customer who bought the same product on two orders comes back twice. IN and EXISTS never repeat an outer row.

-- A: returns 4 rows
SELECT c.name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
JOIN order_items AS i ON i.order_id = o.id
WHERE i.product_id = 6;

-- B: returns 3 rows
SELECT name
FROM customers
WHERE id IN (
  SELECT o.customer_id
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.id
  WHERE i.product_id = 6
);

Loading your workspace…