Lesson 3 of 9 · Lab
Find out why rows don't match in a join
Join attaches columns from a second table wherever a key matches. Rows that match go to Inner. Rows that don't go to Left or Right, and those two outputs are where you find out what went wrong.
Join has two inputs and three outputs: Left holds left rows with no match, Inner the matched pairs and Right the right rows nobody matched. Inner plus Left must add up to the rows that came in.
Marit, who owns fictional Kramerica Bikes, asks how much of the trade is visitors. The register says whether each customer is a visitor, local or corporate, so you attach it by customer_id, a column you dropped in the first course, Build a report from a CSV export. Walk-ins and staff hires have no customer ID, so the answer can only cover registered customers. After Join, the flow keeps finished hires, works out net and totals by segment.
There are 1,218 bookings. Will each match a customer?
No. 614 are walk-ins and staff hires with no customer ID, which is correct. The lab finds out why some of the rest don't match.
Attach the register and read what's left
- In this live Strata flow, Oplema's browser app for data prep, Join is selected and red: it has no key yet, so the three blocks after it are red too. Under Join Keys, select Add. Set Left Column and Right Column to
customer_id. - Open OUTPUT INNER: 466 rows. Open OUTPUT LEFT: 752 bookings found no customer.
- Find in OUTPUT LEFT why registered customers miss, and fix it before Join. Check: OUTPUT INNER holds 591 rows and OUTPUT LEFT 627.
If you're stuck
Scroll down OUTPUT LEFT: past the rows with no
customer_id, Old Town's web bookings sayc-1002, where the register writesC-1002. In the palette, select Prep and drop Cleanse onto the line from Union to Join: it wires itself in. In Cleanse, open Per-Column Overrides, select ▸ besidecustomer_idand set Modify Case to Upper. - Read OUTPUT LEFT again: 614 have no
customer_id, and 13 have IDs fromC-1901up, which the register doesn't have. - Open OUTPUT RIGHT: 58 customers with no September booking.
- Select Summarize, the last block, for finished hires by segment.
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
472 finished hires by registered customers: visitor 342 with €13,120.40 net, local 128 with €4,569.60 and corporate 2 with €893.20. 591 + 627 = 1,218.
Every left row is matched or explained
125 bookings (€3,368.00 with VAT) were missing only because of case. The other 627 are explained: 614 with no customer at all (€26,658.00) and 13 by customers who signed up after Arne, the bookkeeper who retired in September, exported the register (€628.00). Those 13 need the register refreshed, not a fix in the flow.
The two corporate hires are Harbourlight Ferries' outing, €1,001.00 at Harbour on September 18, and a coffee supplier, Vandelay Coffee Supply, which hired 3 e-bikes from Station Road for €115.50.
Your line for Marit: "Of 472 finished hires by registered customers, 342 are visitors. 614 bookings have no customer, so I can't say about those."
See the Join and Cleanse references. For later: Inner vs left joins covers what a key promises.
Common join key mistakes
- Reading only Inner. A join that drops rows still succeeds. Check that Inner plus Left equals the rows that went in.
- Fixing the whole table's case. Upper-casing every text column also changes
status, and the finished-hires filter stops matching. - Treating every miss as an error. Walk-ins should have no customer. Name each group of misses before you fix anything.
Unmatched rows in SQL and Excel
SELECT b.*, c.segment
FROM bookings b
LEFT JOIN customers c ON upper(b.customer_id) = c.customer_id;
SELECT b.* FROM bookings b
WHERE NOT EXISTS (SELECT 1 FROM customers c
WHERE upper(b.customer_id) = c.customer_id);SQL has no Left output, so the second query finds the misses. Excel's =XLOOKUP(D2, Customers!A:A, Customers!C:C, "none") ignores case, which hides this problem, so count the none results.