SQL IN vs JOIN with Duplicates

In Lesson 10.2 · IN with subqueries, the subquery read suppliers, where each supplier appears once. A join to that table would have given the same rows, so choosing between the two was a matter of taste. It stops being one when the other table can match a row many times. Which customers have bought something from Outdoor Decor (category 8)? Here it is as a join, with the order and product shown so you can see where each row comes from:

select c.first_name, c.last_name, o.order_id, p.product_name
from customers as c
join orders as o on o.customer_id = c.customer_id
join order_items as oi on oi.order_id = o.order_id
join products as p on p.product_id = oi.product_id
where p.category_id = 8
order by c.last_name, o.order_id, p.product_name

Two customers, four rows. Owen Castellano bought a Garden Gnome and a Solar Lantern in order 22, and Mason Doyle bought the same pair in order 9. A join returns one row for every combination that matches, so each customer appears once per matching order line. That's right when the question is about order lines. For "which customers", it's two customers listed twice.

IN asks yes or no

Move the order tables into a subquery and filter with IN:

select first_name, last_name
from customers
where customer_id in (
  select o.customer_id
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  join products as p on p.product_id = oi.product_id
  where p.category_id = 8
)
order by last_name

Each customer is now checked once: is your customer_id in the list? The subquery returns Owen's id twice, but the answer is yes either way, so he appears once. IN filters the outer rows and never multiplies them, however many duplicates the subquery returns. Run explain on it and DuckDB shows a HASH_JOIN with Join Type: SEMI: a join that keeps a left row if it has any match, and returns it once. EXISTS (Lesson 10.3) is the same semi-join written another way.

Why not just add DISTINCT?

select distinct on the join returns two customers here too, and for a plain list it works. It stops working once you add something up. How much stock is on the shelf for products that have sold at least once?

select
  (select sum(p.quantity_on_hand)
   from products as p
   join order_items as oi on oi.product_id = p.product_id) as with_join,
  (select sum(distinct p.quantity_on_hand)
   from products as p
   join order_items as oi on oi.product_id = p.product_id) as with_sum_distinct,
  (select sum(quantity_on_hand)
   from products
   where product_id in (select product_id from order_items)) as with_in

That returns 2,300, 893, and 933, and only the last is right. The join counts each product's stock once per order line: Monstera Deliciosa's 40 units are counted four times, because it was on four order lines. sum(distinct ...) looks like the fix, but it removes repeated values, not repeated products. Monstera Deliciosa and All-Purpose Fertilizer both have 40 units in stock, so it counts 40 once and comes up 40 short. IN keeps each of the 22 products that have sold, once each, so the sum is right. The same row multiplication behind the join's total is the subject of inflated counts after a join.

When the join is the right tool

Use a join when the result needs columns from the other table: the order date, the product name, the quantity. Each of those rows is a real fact about one order line, so they aren't duplicates. Use IN or EXISTS when the other table only decides which rows to keep. A NULL in an IN list does no harm: it can't match anything, and every other value still matches as usual. The trouble only starts with NOT IN, which Lesson 10.5 covers.