---
title: "Inner vs left join and other join types | Oplema"
description: "Learn what inner, left, right and full joins keep, how repeated keys multiply rows and how to write each join in SQL and Excel, then audit the result."
url: "https://oplema.com/learn/data-preparation/joins"
updated: "2026-11-04"
---

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
| Type | Keeps | Customers in the result | Rows |
| --- | --- | --- | --- |
| Inner | Matches only | 1, 2 | 2 |
| Left | Every order | 1, 2, 9 | 3 |
| Right | Every customer | 1, 2, 3 | 3 |
| Full outer | Everything | 1, 2, 3, 9 | 4 |

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](https://oplema.com/learn/data-preparation/what-is-data-prep) 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\_id | customer | region | valid\_from | rebate\_pct |
| --- | --- | --- | --- | --- |
| C1 | Starhopper | North | 2024-01-01 | 10 |
| C2 | Wonka | South | 2024-01-01 | 0 |
| C3 | Excalibur | West | 2024-01-01 | 0 |
| C5 | Dunder Mifflin | South | 2024-06-01 | 0 |
| C5 | Dunder Mifflin | North | 2026-08-01 | 0 |

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](https://oplema.com/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](https://oplema.com/learn/export-to-report/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**.

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](https://oplema.com/docs/strata/combine/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](https://oplema.com/learn/data-preparation/formulas) 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](https://oplema.com/learn/business-intelligence/joins-integration) shows what it does to a dashboard.

## Common join mistakes

- **A repeated lookup key.** Check for it first, as in [finding duplicate rows](https://oplema.com/learn/data-preparation/unique-and-duplicates).
- **Keys that only look equal.** A stray space or different case silently fails to match, so [clean the keys](https://oplema.com/learn/data-preparation/cleanse-and-nulls) 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.
