Joins and set operations

⏱ 30 min

Hook

Your pipeline's revenue number is exactly double what finance reports. Nothing looks wrong: the join runs, the sum runs, the dashboard renders. But somewhere you joined orders to a table that has two rows per order, and every order got counted twice. A join did not error — it quietly changed your row count, and the wrong number shipped. Joins are where correct-looking SQL goes wrong most often, so this is where a data engineer's SQL gets careful.

Concept

A join combines rows from two tables by matching a condition, usually a key. The type of join decides what happens to rows that have no match, and that decision is the whole game.

Think of two guest lists for a party. An inner join admits only people on both lists. A left join admits everyone on the left list, and fills in blanks for anyone missing from the right. The precise version:

Join type	Keeps	Pipeline use
INNER JOIN	Only rows with a match in both tables	Enrich rows you know exist in both
LEFT JOIN	All left rows; NULLs where the right has no match	Keep every source row even if lookup is empty
Anti-join	Left rows with no right match (LEFT + IS NULL)	Find what is missing: orphans, gaps, leaks

Two failure modes matter more than the syntax.

First, an inner join silently drops rows. If you INNER JOIN orders to customers and one order references a customer that was deleted, that order vanishes from your result — no error, just a smaller number. When every source row must survive, use a LEFT JOIN and handle the NULLs deliberately.

Second, a join can multiply rows. If the right table has more than one row per key — two shipping addresses per customer, two line items per order — each left row is repeated once per match. This fan-out is what doubled the revenue in the hook. Before you sum anything after a join, know how many rows per key the other table holds.

Set operations stack the results of two queries that have the same columns, comparing whole rows instead of joining on a key:

• UNION ALL concatenates both result sets, keeping duplicates. It is cheap because it does not deduplicate.
• UNION concatenates and removes duplicate rows. It costs a sort or hash to find the duplicates.
• EXCEPT returns rows in the first query that are not in the second — ideal for "what did we ship that we never invoiced?"
• INTERSECT returns rows present in both.

The rule of thumb: reach for UNION ALL unless you actually need duplicates removed, and use EXCEPT or an anti-join whenever the question is "what is in A but not in B?"

🧠 Knowledge check

1. You must produce one output row for every order, even orders whose customer record was deleted. Which join do you use?

a) INNER JOIN, because it is the defaultb) LEFT JOIN from orders to customers, keeping orders with no customer matchc) INNER JOIN from customers to ordersd) A set operation, because joins cannot keep unmatched rows

2. After joining orders to a line-items table, your total revenue doubles. What is the most likely cause?

a) The join condition is wrong and matched nothingb) Fan-out: each order matched multiple line-item rows, repeating the orderc) UNION removed rows it should have keptd) The database is broken

Worked example

A shipping team and a billing team run separate systems. Every shipped order should have an invoice, but revenue leaks when something ships and is never billed. Let me find the leak with a reconciliation query.

Here are the two record sets — shipments and invoices, each just a list of order IDs:

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE shipments (order_id INTEGER);
CREATE TABLE invoices  (order_id INTEGER);
INSERT INTO shipments VALUES (5001), (5002), (5003), (5004), (5005);
INSERT INTO invoices  VALUES (5001), (5002), (5004), (5006);
""")

First, how many orders were both shipped and invoiced? That is the inner join — the healthy, matched rows:

matched = con.execute("""
    SELECT COUNT(*)
    FROM shipments s
    JOIN invoices i ON s.order_id = i.order_id
""").fetchone()[0]
print("shipped and invoiced:", matched)

shipped and invoiced: 3

Three matched. But five orders shipped, so two shipped without an invoice — the leak. I find them with an anti-join: a LEFT JOIN that keeps every shipment, then a filter for the rows where the invoice side came back NULL.

leaks = con.execute("""
    SELECT s.order_id
    FROM shipments s
    LEFT JOIN invoices i ON s.order_id = i.order_id
    WHERE i.order_id IS NULL
    ORDER BY s.order_id
""").fetchall()
print("shipped but never invoiced:", [r[0] for r in leaks])

shipped but never invoiced: [5003, 5005]

The same question answers even more directly with EXCEPT — shipments minus invoices — which compares whole rows and needs no join condition:

leaks_set = con.execute("""
    SELECT order_id FROM shipments
    EXCEPT
    SELECT order_id FROM invoices
    ORDER BY order_id
""").fetchall()
print("EXCEPT result:", [r[0] for r in leaks_set])

EXCEPT result: [5003, 5005]

Both approaches find orders 5003 and 5005. The anti-join is the tool when you also need columns from the left table; EXCEPT is the tool when you only need the keys and want the shortest query. Notice what neither approach did: throw an error. The leak was invisible until you asked for it directly.

Hands-on

Your turn, with a different reconciliation. The exercise below gives you a users table and an active_sessions table and asks you to find users who have never started a session, then the sessions whose user no longer exists — a gap-and-orphan check on both sides.

▶ SQL Kingdomde102-reconcile-users-sessions

Practice in SQL Kingdom ↗

Success criteria: your anti-join returns exactly the users with no session, and your EXCEPT/orphan query returns exactly the sessions with no matching user. SQL Kingdom checks both result sets against the expected keys automatically.

Recap

• You can pick a join type on purpose: INNER to keep only matches, LEFT to keep every source row, an anti-join to find what has no match.
• You can explain the two silent failures — dropped rows from an inner join and fan-out from a one-to-many join — before they corrupt a number.
• You can reconcile two datasets with EXCEPT, INTERSECT, and UNION ALL, and choose UNION ALL unless you truly need duplicates removed.

Next up: window functions, which let you keep every row from these joins and still compute per-group totals, rankings, and one-row-per-key deduplication.

🧠 Knowledge check

1. Which query returns order IDs that were shipped but never invoiced?

a) SELECT order_id FROM shipments INTERSECT SELECT order_id FROM invoicesb) SELECT order_id FROM shipments EXCEPT SELECT order_id FROM invoicesc) SELECT order_id FROM invoices EXCEPT SELECT order_id FROM shipmentsd) SELECT order_id FROM shipments UNION ALL SELECT order_id FROM invoices

2. You want to stack two result sets and you know they contain no duplicates. Which operator is cheapest?

a) UNION, because it is saferb) UNION ALL, because it skips the deduplication stepc) INTERSECT, because it compares rowsd) EXCEPT, because it removes rows

📌 Key takeaways

• Join type is a decision: INNER drops unmatched rows, LEFT keeps them, an anti-join isolates the unmatched ones.
• Fan-out from a one-to-many join repeats left rows and double-counts sums — check row-per-key cardinality before aggregating.
• Use EXCEPT/INTERSECT to compare whole rows, and prefer UNION ALL unless you need duplicates removed.