Lesson 10 of 15 · Lab

Inner vs left: joining tables by key

A join adds columns from a second table by matching a key, such as a customer ID. An inner join keeps only the rows that match. A left join keeps every row from the first table and leaves the new columns empty where nothing matched.

Inner, left, right and full joins

The join type decides what happens to rows without a match. Take orders for customers 1, 2 and 9, and a customer table holding customers 1, 2 and 3.

Orders joined to customers
TypeKeepsCustomers in the resultRows
InnerMatches only1, 22
LeftEvery order1, 2, 93
RightEvery customer1, 2, 33
Full outerEverything1, 2, 3, 94

Use left when the first table defines what you're counting, such as every order in a sales total. Use inner only when dropping unmatched rows is the point. Right is a left join with the tables swapped, and full outer compares two lists.

Make a lookup table one row per key

Fictional Vandelay Coffee Supply has 13 booked Q3 invoices that need a region from its customer register.

Open the customer register
Vandelay's customer register, with a row for each version of a customer
customer_idcustomerregionvalid_fromrebate_pct
C1StarhopperNorth2024-01-0110
C2WonkaSouth2024-01-010
C3ExcaliburWest2024-01-010
C5Dunder MifflinSouth2024-06-010
C5Dunder MifflinNorth2026-08-010
13 rows go in and 13 come out. Did every invoice match exactly once?

No. Dunder Mifflin's two invoices each match two register rows, and two C4 invoices match nothing. The errors cancel in the count, and only Join's outputs show them.

  1. In this live flow in Strata, Oplema's browser app for data prep, Join opens on Output Left: find two C4 invoices with no customer.
  2. Select Summarize. Dunder Mifflin is in both regions, and C4's customer is nowhere.
  3. Select the wire from the lower Manual Input to Join, press Delete. The flow stays red until reconnected.
  4. From the Prep tab, drag Sort to the lower gap, Unique beside it. If Unique is off-screen in the Prep row, hold Shift and scroll.
  5. Manual Input is now wired to Sort. Drag dot to dot: Sort to Unique, Unique's upper right to Join's lower left. New to wiring blocks? Build your first data flow shows how.
  6. Sort: Column valid_from, Direction Descending. In Unique, clear the top Match On box, check customer_id. Summarize shows Dunder Mifflin once.
  7. C4 is Initech, South since September 1. Don never registered it. Select the lower Manual Input, hover under its last row, select +, then the first new cell, type C4, Initech, South, 2026-09-01, 0, using Tab. Select Summarize.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What Summarize should show

Summarize has four rows: Starhopper €4,320 and Dunder Mifflin €1,690 in North, Wonka €2,900 and Initech €3,000 in South. North is €6,010 and South €5,900, on 13 invoices again.

A row count can't audit a join

Before the repair, South totaled €4,590, with Dunder Mifflin counted in both regions and Initech in neither. Now look at the finance report: South, €4,590. Sydney's lookup is this join. So on every join, read the unmatched outputs and make the lookup key unique.

Strata's Join differs from SQL here. Its Inner output holds the matches, while Left and Right hold only the unmatched rows, so SQL's LEFT JOIN equals Inner plus Left.

Keeping the newest register row means Dunder Mifflin's July invoice counts as North. North now leads by €110 at list prices, before any rebate. Calculated columns comes back to that.

Repeated keys

If a key repeats in the lookup table, each match comes out once per copy and adds up twice, which is fan-out: joining data for BI shows what it does to a dashboard.

Common join mistakes

  • A repeated lookup key. Check for it first, as in finding duplicate rows.
  • Keys that only look equal. A stray space or different case silently fails to match, so clean the keys first.
  • Filtering after a left join. WHERE c.region = 'North' drops the unmatched rows and turns the left join into an inner one. Put the condition in the ON clause to keep them.

Inner and left joins in SQL and Excel

SELECT i.invoice_id, c.region
FROM invoices i
LEFT JOIN customers c ON i.customer_id = c.customer_id

Swap LEFT for INNER, RIGHT or FULL OUTER to get the other types. Adding WHERE c.customer_id IS NULL lists the unmatched invoices, as Join's Left output does.

In Excel, XLOOKUP and VLOOKUP act like a left join that returns the first match only, so a repeated key stays hidden, and a missing key gives #N/A. Power Query's Merge offers all four join types, and it multiplies rows as SQL does.