Join
Join combines two tables side by side where key columns match. Inner has the matched rows. Left and Right have the rows from each side that found no match. Inner drops the right key because it repeats the left one. Any other column name found in both tables gets _left and _right added.
Join customers to orders
The Left input has three customers. The Right input has three orders, joined on id.
Join interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| id | name | city | amount |
|---|---|---|---|
| 1 | Ada | Oslo | 120 |
| 2 | Lin | Rome | 75 |
- The preview opens on OUTPUT LEFT with one row: Sam, id 3. Sam has no order.
- Select OUTPUT INNER above the preview, or the middle output port on the block. This is the joined table: Ada with 120 and Lin with 75, with name, city and amount together.
- Select OUTPUT RIGHT. It has one row: the order with id 4 and amount 40, which matches no customer.
When to use
Use Join to add columns from a related table, such as adding order amounts to customers, and to see what failed to match. To add a label from a small reference table while keeping every row, Lookup is simpler. To stack rows from similar tables, use Union. To link names that are spelled differently, use Fuzzy Match.
To join three or more tables at once, use Join Multiple.
To add labels while keeping every row, use Lookup.
To match text that is spelled differently, use Fuzzy Match.
Inputs and outputs
Inputs
2 ports- LeftRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
- RightRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
3 ports- Left
- Inner
- Right
Configure the block
- Join Keys
Column pairs to match between the inputs.
- Columns
Shape the Inner output: keep, drop, reorder, rename or cast each column. Use the output names: the right key is dropped, and shared names end in _left or _right. Leave empty to keep every column. Left and Right don't change.
Join FAQs
- How do I join or merge two files, such as two CSV files?
- Add an Input block for each file, connect them to Left and Right, then set Join Keys.
- Which output has the joined table?
- Inner has the matched rows. Left and Right hold only the rows from each side that found no match.
- What happens to columns with the same name?
- Inner drops the right key because it repeats the left one. Any other column name found in both tables gets _left and _right added.
- What if a key repeats?
- Inner has one row for each matching pair, so repeated keys can add rows. To keep the row count fixed when adding a label, use Lookup.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.