anti-joins (finding what's missing)

A normal join finds matches. An anti-join finds rows that don't have a match. "Customers who never ordered", "products never sold", "users who signed up but never logged in". Super common question, and there are three ways to write it, one of which has a nasty bug.

way 1: NOT EXISTS (use this)

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

Karan and Arjun. Reads almost like english, "customers where there doesn't exist an order for them". Handles NULLs correctly and databases optimize it really well.

way 2: LEFT JOIN + IS NULL

SELECT c.first_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Same result. LEFT JOIN keeps everyone, the unmatched ones have NULLs on the order side, keep just those. Check for NULL on a column that can never be NULL normally (like the primary key), otherwise you might catch real rows that just happen to have a NULL.

way 3: NOT IN (careful!)

SELECT first_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Works here. But see what happens if there's a NULL in the subquery:

SELECT first_name
FROM customers
WHERE customer_id NOT IN (1, 2, NULL);

Returns nothing. Zero rows. Not even Karan.

Why? 5 NOT IN (1, 2, NULL) means 5 <> 1 AND 5 <> 2 AND 5 <> NULL. That last one is NULL (unknown), and true AND NULL is NULL, so the WHERE fails for every single row.

So if orders.customer_id ever had a NULL (guest checkout, deleted user, whatever), your NOT IN query silently returns empty. No error. That's why people say just use NOT EXISTS.

If you must use NOT IN, filter the NULLs out:

WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)

semi-join (the opposite)

"customers who have at least one order", just EXISTS:

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

Better than a JOIN for this because a join would give Aman 3 times (3 orders), EXISTS gives him once.

with extra conditions

"customers who never had a delivered order":

SELECT c.first_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id AND o.status = 'delivered'
);

Karan, Arjun, and Riya (she has an order but it's pending).

stuff that trips people up

• NOT IN with NULLs, returns nothing. The classic.
• LEFT JOIN anti-join checking IS NULL on a nullable column.
• putting the extra condition in WHERE instead of ON for the LEFT JOIN version (see join predicates topic).
• using JOIN + DISTINCT when you just wanted EXISTS.

practice

Check queries.sql.

-- anti-joins
-- run sample-data.sql first

-- examples ---------------------------------------------------------

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

SELECT c.first_name FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- the NULL trap, returns nothing
SELECT first_name FROM customers WHERE customer_id NOT IN (1, 2, NULL);


-- practice ---------------------------------------------------------

-- 1. customers with no address on file (use NOT EXISTS)
-- 2. same thing but with LEFT JOIN / IS NULL
-- 3. customers who never bought anything from Stationery
-- 4. employees who are nobody's manager (no one reports to them)
-- 5. customers who have ordered but never had an order cancelled
-- 6. predict: what does this return? then run it.
--    SELECT name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees);


-- answers ----------------------------------------------------------

-- 1
SELECT c.first_name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM addresses a WHERE a.customer_id = c.customer_id);

-- 2
SELECT c.first_name FROM customers c
LEFT JOIN addresses a ON c.customer_id = a.customer_id
WHERE a.address_id IS NULL;

-- 3
SELECT c.first_name FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
  WHERE o.customer_id = c.customer_id AND p.category = 'Stationery'
);

-- 4
SELECT e.name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.employee_id);

-- 5
SELECT c.first_name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'cancelled');

-- 6
-- nothing! Vikram's manager_id is NULL, so the subquery has a NULL in it, so NOT IN fails for everyone.
-- fix:
SELECT name FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);