Joins, NULL, and Set Operations: Keeping Multi-Table Queries Correct

Most join bugs are not syntax errors. The query runs, returns plausible rows, and quietly changes the meaning of the data.

Begin with cardinality

Before writing SQL, state the relationship:

• one customer has many orders;
• one order has many items;
• products and orders are many-to-many through order items.

If ten orders become eighty rows after joining items, that may be correct. If the next step sums orders.total, it is probably wrong because each order total is repeated once per item.

In plain terms: a join combines matching rows; it does not know which numbers are safe to add afterward. Predict the row count before trusting the aggregate.

INNER and LEFT JOIN answer different questions

SELECT c.id, o.id
FROM customer c
JOIN orders o ON o.customer_id = c.id;

This asks for customers that have matching orders. To keep customers with no order, use a left join:

SELECT c.id, o.id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id;

Condition placement matters:

-- Keeps every customer; only paid orders may match.
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid'

-- Removes customers without a paid order; behaves like an inner join.
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'

The first condition controls matching. The second filters the result after unmatched rows have received NULL on the right side.

Anti-joins: prefer NOT EXISTS

This query looks reasonable but is fragile:

SELECT * FROM paper
WHERE venue_id NOT IN (SELECT venue_id FROM blacklist);

If the subquery returns one NULL, every comparison can become UNKNOWN, and the query may return no rows. Express the intent directly:

SELECT p.*
FROM paper p
WHERE NOT EXISTS (
  SELECT 1
  FROM blacklist b
  WHERE b.venue_id = p.venue_id
);

EXISTS and NOT EXISTS test whether a matching row exists; they do not need to compare a value with a list containing unknowns.

UNION is not concatenation

UNION combines results and removes duplicates. UNION ALL simply appends them.

SELECT email FROM current_member
UNION ALL
SELECT email FROM archived_member;

Use UNION ALL when both sources represent distinct events or when duplicates are meaningful. Use UNION only when the result must be a set and the cost of deduplication is justified.

INTERSECT keeps rows in both inputs; EXCEPT keeps rows from the left input that do not appear on the right. All set operations require compatible column counts and types.

A correctness checklist

Question	Why it matters
What is the relationship cardinality?	Predicts multiplication of rows
Should unmatched left rows survive?	Chooses inner versus left join
Does a right-side filter belong in ON or WHERE?	Changes outer-join meaning
Can the subquery contain NULL?	Makes NOT IN unsafe
Are duplicates meaningful?	Chooses UNION ALL versus UNION

Review card

• Predict join cardinality before aggregating.
• A filter in WHERE can cancel the outer part of a left join.
• Use IS NULL, never = NULL.
• Prefer NOT EXISTS for NULL-safe anti-joins.
• UNION ALL appends; UNION appends and deduplicates.

SELECT c.id, o.id
FROM customer c
JOIN orders o ON o.customer_id = c.id;

SELECT c.id, o.id
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id;

-- 保留所有 customer；只有 paid order 能配對。
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.status = 'paid'

-- 移除沒有 paid order 的 customer，效果接近 inner join。
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'

SELECT * FROM paper
WHERE venue_id NOT IN (SELECT venue_id FROM blacklist);

SELECT p.*
FROM paper p
WHERE NOT EXISTS (
  SELECT 1
  FROM blacklist b
  WHERE b.venue_id = p.venue_id
);

SELECT email FROM current_member
UNION ALL
SELECT email FROM archived_member;