Joins I — semantics

LEFT-turned-INNER, NULL keys, anti-join three ways, ON vs WHERE in outer joins, self-join.

• ★The LEFT JOIN that turned INNER

  A WHERE filter on the right table discards the NULL-extended rows — your LEFT JOIN silently becomes INNER; right-side filters belong in ON.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE customers(id INT, name VARCHAR); INSERT INTO customers VALUES (1,'Ada'),(2,'Bo'); CREATE TABLE orders(customer_id INT, order_date DATE); INSERT INTO orders VALUES (1, DATE '2024-02-01'),(1, DATE '2023-12-15');

  The trap

  SELECT c.name, COUNT(o.customer_id) AS n FROM customers c LEFT JOIN orders o ON o.customer_id=c.id WHERE o.order_date >= DATE '2024-01-01' GROUP BY c.name ORDER BY c.name;

  Ada

  1

  The fix

  SELECT c.name, COUNT(o.customer_id) AS n FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.order_date >= DATE '2024-01-01' GROUP BY c.name ORDER BY c.name;

  Ada	1
  Bo	0

  the 'all customers incl. zero orders' report quietly drops every zero-order customer — the rows it exists to show.

• Join keys with NULLs never match

  NULL = NULL is UNKNOWN, so rows with a NULL join key quietly vanish from an INNER join.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE carts(cart VARCHAR, zone_id INT); INSERT INTO carts VALUES ('c1',10),('c2',NULL),('c3',20),('c4',NULL); CREATE TABLE zones(zone_id INT, zone VARCHAR); INSERT INTO zones VALUES (10,'North'),(20,'South');

  The trap

  SELECT c.cart, z.zone FROM carts c JOIN zones z ON z.zone_id = c.zone_id ORDER BY c.cart;

  c1	North
  c3	South

  The fix

  SELECT c.cart, COALESCE(z.zone, 'Unassigned') AS zone FROM carts c LEFT JOIN zones z ON z.zone_id = c.zone_id ORDER BY c.cart;

  c1	North
  c2	Unassigned
  c3	South
  c4	Unassigned

  Two carts with a NULL zone quietly dropped from the compliance count — a NULL join key is never equal to anything, so the INNER join skips those rows.

• Anti-join three ways

  LEFT...IS NULL, NOT EXISTS, and NOT IN all express 'no match' — but NOT IN carries the P01 NULL trap; NOT EXISTS is the safe habit.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE students(sid INT); INSERT INTO students VALUES (1),(2),(3); CREATE TABLE assign(student_id INT); INSERT INTO assign VALUES (1),(NULL);

  The trap

  SELECT sid FROM students WHERE sid NOT IN (SELECT student_id FROM assign) ORDER BY sid;

  0 rows

  The fix

  SELECT sid FROM students s WHERE NOT EXISTS (SELECT 1 FROM assign a WHERE a.student_id = s.sid) ORDER BY sid;

  2

  3

  The NOT IN spelling returned empty — one NULL student_id in the assignment list makes NOT IN never TRUE, where LEFT...IS NULL and NOT EXISTS both correctly list the unassigned students.

• ON vs WHERE in outer joins

  In an OUTER join, a condition in ON filters the join; the same condition in WHERE filters the result — different meaning.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE members(member VARCHAR); INSERT INTO members VALUES ('Ada'),('Ben'),('Cy'); CREATE TABLE attendance(member VARCHAR, term VARCHAR); INSERT INTO attendance VALUES ('Ada','spring'),('Cy','fall');

  The trap

  SELECT m.member FROM members m LEFT JOIN attendance a ON a.member = m.member WHERE a.term = 'spring' ORDER BY m.member;

  Ada

  The fix

  SELECT m.member FROM members m LEFT JOIN attendance a ON a.member = m.member AND a.term = 'spring' ORDER BY m.member;

  Ada

  Ben

  Cy

  New choir members with no spring attendance vanished from the roster — the term filter in WHERE deleted the NULL-extended rows and turned the LEFT JOIN inner.

• Self-join basics

  A table joined to itself (employee -> manager) needs distinct aliases and a non-reflexive condition.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE staff(id INT, name VARCHAR, mentor_id INT); INSERT INTO staff VALUES (1,'Io',NULL),(2,'Pax',1),(3,'Rae',1);

  The trap

  SELECT s.name, m.name AS mentor FROM staff s JOIN staff m ON m.id = s.mentor_id ORDER BY s.name;

  Pax	Io
  Rae	Io

  The fix

  SELECT s.name, m.name AS mentor FROM staff s LEFT JOIN staff m ON m.id = s.mentor_id ORDER BY s.name;

  Io	NULL
  Pax	Io
  Rae	Io

  The INNER self-join dropped Io, the root contributor with no mentor — a table joined to itself needs LEFT to keep the roots, and the two aliases are the whole trick.

• FULL OUTER JOIN reconciliation

  FULL OUTER JOIN keeps both sides' unmatched rows — with COALESCE keys and a side-status column it is the reconciliation report.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE shelf(item VARCHAR, n INT); INSERT INTO shelf VALUES ('X',1),('Y',2); CREATE TABLE loans(item VARCHAR, n INT); INSERT INTO loans VALUES ('Y',2),('Z',3);

  The trap

  SELECT s.item, 'ok' AS status FROM shelf s JOIN loans l ON l.item = s.item ORDER BY s.item;

  Y

  ok

  The fix

  SELECT COALESCE(s.item, l.item) AS item, CASE WHEN s.item IS NULL THEN 'b_only' WHEN l.item IS NULL THEN 'a_only' WHEN s.n IS DISTINCT FROM l.n THEN 'diff' ELSE 'ok' END AS status FROM shelf s FULL OUTER JOIN loans l ON l.item = s.item ORDER BY item;

  X	a_only
  Y	ok
  Z	b_only

  The reconciliation missed the shelf-only and loan-only items — an INNER JOIN shows only agreement, where a FULL OUTER JOIN with a COALESCE key and a side-status CASE reports only-in-A, only-in-B, and matched.