Join
Bring columns from a second table into this one. Match the rows on a shared column.
What it does
Join looks up each row of your table in a second table. It then adds the columns of that second table to yours.
Excel users know this as VLOOKUP. Join does the same lookup, for every row, in one step. It can also keep rows that find no match.
You need two tables in the workspace. Load a second file, or open a second view of a file.
Join two tables
- Type join in the Search transforms box, in the Pipeline panel.
- Select Join in the results. The Join panel opens.
- Open Join with table, under 1. Select table & join type.
- Pick a table. Each entry shows a file name and a view name.
- Pick a Join type: Inner, Left, Right, or Full.
- Go to 2. Match columns. Set Left key (current table).
- Set Right key to the matching column in the other table.
- Read the Join Preview. It shows the first five rows of the result.
- Click Apply.
The step appears in your pipeline. You can edit or delete it later.
With no second table loaded, the panel shows a warning instead of the picker: No other files uploaded. Upload another CSV to join.
The four join types
Your current table is the left table. The table you pick is the right table.
| Type | Rows you get |
|---|---|
| Inner | Only rows that match in both tables. |
| Left | All your rows. Empty cells where the right table has no match. |
| Right | All rows of the right table. Empty cells where your table has no match. |
| Full | All rows from both tables, matched where possible. |
A worked example
Your table, orders:
| order_id | customer_id | total |
|---|---|---|
| A-1 | C1 | 120 |
| A-2 | C2 | 80 |
| A-3 | C9 | 45 |
The right table, customers:
| customer_id | city |
|---|---|
| C1 | Pune |
| C2 | Kochi |
A Left join on customer_id gives this:
| order_id | customer_id | total | city |
|---|---|---|---|
| A-1 | C1 | 120 | Pune |
| A-2 | C2 | 80 | Kochi |
| A-3 | C9 | 45 |
Order A-3 has no customer record. A Left join keeps it and leaves city empty. An Inner join drops it.
Pick the columns you keep
Step 3. Columns to include from right table (leave empty for all) is optional.
Leave it empty and the result holds every column of both tables. The key column then appears twice.
Select two or three columns to keep the result narrow.
The other table stays live
The step remembers the view you picked. It does not copy that data.
Change the other file or its pipeline later. This step then runs again with the new values.
Example: you correct a city name in the customer file. Your joined table shows the correction.
Tips
- Both keys must hold the same kind of value. Use Convert Type when the types differ.
- An inner join with zero rows often means the keys differ. Look for extra spaces or case differences.
- Row counts can grow. One left row that matches three right rows gives three result rows.
- Use step 3 to drop the duplicate key column from the result.
For SQL users
A left join on customer_id, with no column selection, runs as:
SELECT "__mammoth_l".*, "__mammoth_r".*
FROM "orders" AS "__mammoth_l"
LEFT JOIN "customers" AS "__mammoth_r"
ON "__mammoth_l"."customer_id" = "__mammoth_r"."customer_id"Related Operations
- Unnest - Explode delimited column into rows
- Fill Missing - Fill null values
- JSON Extract - Extract data in JSON format into columns