---
title: "Find rows that don't match in a join | Oplema"
description: "Learn to read a join's Left and Right outputs in Strata, fix keys that differ only in case and explain every booking that didn't match the register."
url: "https://oplema.com/learn/many-sources/find-unmatched-join-rows"
updated: "2026-11-04"
---

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](https://oplema.com/learn/export-to-report/first-data-flow), 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

1. In this live [Strata](https://oplema.com/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`.
2. Open **OUTPUT INNER**: 466 rows. Open **OUTPUT LEFT**: 752 bookings found no customer.
3. 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 say `c-1002`, where the register writes `C-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 **▸** beside `customer_id` and set **Modify Case** to **Upper**.
4. Read **OUTPUT LEFT** again: 614 have no `customer_id`, and 13 have IDs from `C-1901` up, which the register doesn't have.
5. Open **OUTPUT RIGHT**: 58 customers with no September booking.
6. Select **Summarize**, the last block, for finished hires by segment.

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](https://oplema.com/docs/strata/combine/join) and [Cleanse references](https://oplema.com/docs/strata/prep/cleanse). For later: [Inner vs left joins](https://oplema.com/learn/data-preparation/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.
