Set operations

UNION dedups vs UNION ALL; position-based column alignment; NULL equality for dedup; EXCEPT/INTERSECT.

• ★UNION dedups; UNION ALL doesn't

  UNION removes duplicates (and pays a sort/hash); UNION ALL keeps them — correctness and cost both hinge on which.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE promo(email VARCHAR); INSERT INTO promo VALUES ('a@x'),('b@x'); CREATE TABLE newsletter(email VARCHAR); INSERT INTO newsletter VALUES ('b@x'),('c@x');

  The trap

  SELECT email FROM promo UNION SELECT email FROM newsletter ORDER BY email;

  a@x

  b@x

  c@x

  The fix

  SELECT email FROM promo UNION ALL SELECT email FROM newsletter ORDER BY email;

  a@x

  b@x

  b@x

  c@x

  The 'total emails to send' count reads 3, not 4 — UNION silently merged the address that was on both lists.

• ★Set ops treat NULLs as equal

  EXCEPT/INTERSECT compare whole rows with NULL-equal semantics — the opposite instinct from NOT IN/=, and exactly why EXCEPT is the safe anti-join for nullable columns.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE morning(item VARCHAR, torque DOUBLE); INSERT INTO morning VALUES ('K1',4.2),('K2',NULL),('K3',4.5); CREATE TABLE evening(item VARCHAR, torque DOUBLE); INSERT INTO evening VALUES ('K1',4.2),('K2',NULL);

  The trap

  SELECT item, torque FROM morning WHERE (item, torque) NOT IN (SELECT item, torque FROM evening) ORDER BY item;

  0 rows

  The fix

  SELECT item, torque FROM morning EXCEPT SELECT item, torque FROM evening ORDER BY item;

  K3

  4.5

  The bike-repair diff came back empty — NOT IN over the nullable torque poisons the result, where EXCEPT compares whole rows treating two NULLs as equal and keeps the one real difference.

• EXCEPT / INTERSECT vs anti/semi-join

  EXCEPT is a set anti-join, INTERSECT a set semi-join — equivalent to NOT EXISTS / EXISTS on full rows.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE morning(sp VARCHAR); INSERT INTO morning VALUES ('a'),('a'),('b'); CREATE TABLE afternoon(sp VARCHAR); INSERT INTO afternoon VALUES ('a');

  The trap

  SELECT sp FROM morning EXCEPT SELECT sp FROM afternoon ORDER BY sp;

  b

  The fix

  SELECT sp FROM morning EXCEPT ALL SELECT sp FROM afternoon ORDER BY sp;

  a

  b

  The morning-minus-departures tally lost a resident — plain EXCEPT dedups as a side effect, so one of the two monarchs the morning legitimately had twice vanished; EXCEPT ALL subtracts one occurrence per match and keeps the other.

• Set ops align by position

  Set operations align columns by position, not by name — swapped columns produce silently plausible garbage.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE log_a(tech VARCHAR, building VARCHAR); INSERT INTO log_a VALUES ('Ada','North Tower'); CREATE TABLE log_b(building VARCHAR, tech VARCHAR); INSERT INTO log_b VALUES ('South Tower','Ben');

  The trap

  SELECT tech, building FROM log_a UNION ALL SELECT building, tech FROM log_b ORDER BY tech;

  Ada	North Tower
  South Tower	Ben

  The fix

  SELECT tech, building FROM log_a UNION ALL SELECT tech, building FROM log_b ORDER BY tech;

  Ada	North Tower
  Ben	South Tower

  The merged inspection log put a building name in the technician column — set operations align by position, not by name, and the swapped SELECT lined up silently.