Joins and anti-joins

Part of the free LogicWiz SQL & Databases course, Interview pack: SQL interview problems. The queries and practice run in the app on real Postgres.

"Find customers who never ordered." Three correct answers exist, one of them breaks on a single NULL, and a fourth "obvious" answer double-counts. This lesson is the difference.

You'll learn to

Write anti-joins with NOT EXISTS and LEFT JOIN … IS NULL
Find pairs with self-joins without duplicates
Delete duplicate rows and keep the lowest id
Avoid the fan-out and ON-vs-WHERE traps

Learn

Three anti-joins

Pattern	Notes
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customerid = c.customerid)	Safest; NULL-proof; usually the fastest plan
LEFT JOIN orders o ON … WHERE o.orderid IS NULL	Fine; test a NOT NULL column of the right table
WHERE customerid NOT IN (SELECT customerid FROM orders)	Breaks if the subquery can return NULL

Customers who never ordered

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

Diya and Arjun signed up but never placed an order.

Pairs without doubles

SELECT a.name AS customer_a, b.name AS customer_b, a.city
FROM customers AS a
JOIN customers AS b
  ON b.city = a.city
 AND b.customer_id > a.customer_id
ORDER BY a.city, a.customer_id, b.customer_id;

b.customer_id > a.customer_id keeps each pair once and never pairs someone with themselves. With <> you would get (Aarav, Meher) and (Meher, Aarav).

Fan-out: join customers → orders → order_items and every customer row is repeated once per item. COUNT(*) and SUM then count too much. Aggregate in a subquery first, or use COUNT(DISTINCT …) when you only need a count.

Deleting duplicates in MySQL: DELETE a FROM contacts a JOIN contacts b ON a.email = b.email AND a.id > b.id;. Postgres writes the same idea as DELETE FROM contacts a USING contacts b WHERE ….

In one line

Anti-join with NOT EXISTS (never NOT IN over a nullable column); pairs with a.id < b.id; dedupe with DELETE … USING on a lower id; count DISTINCT after a fan-out.

Interview corner

How do you delete duplicate rows but keep one?

Self-join and delete the copies with a higher id: DELETE FROM t a USING t b WHERE a.email = b.email AND a.id > b.id;. Or number rows with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete where it is above 1. Then add a UNIQUE constraint so it cannot happen again.

NOT IN, NOT EXISTS or LEFT JOIN … IS NULL?

NOT EXISTS is the safe default: it ignores NULLs and Postgres runs it as an anti-join. LEFT JOIN … WHERE right.pk IS NULL is equivalent. NOT IN returns nothing if the subquery yields a NULL, so avoid it on nullable columns.

Why did my LEFT JOIN behave like an INNER JOIN?

A WHERE condition on the right table (WHERE o.status = 'delivered') is false or unknown for the NULL rows the LEFT JOIN added, so they are removed. Move that condition into the ON clause.