IN, EXISTS and NOT EXISTS

Subqueries that return a list

In module 2 you wrote city IN ('Pune', 'Mumbai') with a typed list. The list can also come from a subquery that returns one column and any number of rows. "Customers who have placed an order" means: customers whose id is in the list of customer_ids in orders.

SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);
-- 1 Asha Verma, 2 Ravi Kumar, 3 Priya Nair, 4 Imran Khan, 5 Sneha Patil

That last point is a real difference from a join. orders JOIN customers would repeat Asha three times. IN (and EXISTS) never multiply rows; each outer row is either kept once or dropped.

Subqueries can be nested. Products that were part of a Delivered order: products whose id is in the order lines whose order_id is in the Delivered orders.

SELECT name FROM products
WHERE id IN (SELECT product_id FROM order_items
             WHERE order_id IN (SELECT id FROM orders WHERE status = 'Delivered'));
-- Basmati Rice 5kg, Toor Dal 1kg, Steel Tiffin Box, Cotton Kurta, Running Shoes, Bluetooth Earbuds

EXISTS: is there at least one?

EXISTS (subquery) is true when the subquery returns at least one row, and false when it returns none. The values in the row do not matter, which is why people write SELECT 1 inside. The subquery usually refers to the outer row (here c.id), which makes it a correlated subquery; topic 3 explains those in detail.

SELECT c.id, c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);
-- the same 5 customers

NOT EXISTS: are there none?

SELECT d.id, d.name
FROM departments AS d
WHERE NOT EXISTS (SELECT 1 FROM employees AS e WHERE e.dept_id = d.id);
-- 5 | Legal

The NOT IN + NULL trap

Try the same question with NOT IN: departments whose id is not in the list of employees' dept_ids.

SELECT name FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees);
-- (no rows!)

Why? Deepak's dept_id is NULL, so the list is (1, 1, 1, 1, 2, 2, 2, 3, 3, NULL, 4). id NOT IN (list) means id <> 1 AND id <> 2 AND ... AND id <> NULL. For Legal, 5 <> NULL is unknown, and true AND unknown is unknown, not true. So no department ever passes. One NULL in the list empties the whole result.

SELECT name FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);
-- Legal

Common mistakes

• NOT IN with a column that can hold NULL. Use NOT EXISTS, or filter out the NULLs inside the subquery.
• Returning two columns from an IN subquery: id IN (SELECT customer_id, status ...) is an error. Return one column.
• Forgetting to link an EXISTS subquery to the outer row. WHERE EXISTS (SELECT 1 FROM orders) is true for every customer as long as any order exists.
• Using a JOIN when you only need "has at least one", then fixing the duplicates with DISTINCT. EXISTS expresses the question directly.