Join Multiple
Join Multiple combines two or more tables on key columns that have the same name in every table. Full outer keeps every row and fills gaps with NULL. Inner keeps only keys found in all tables. The key appears once. Other column names found in more than one table get the input number added, such as val_1 and val_2.
Join three tables on id
Three tables connect to the single input: customers, orders and tiers. They are joined on id using Full outer.
Join Multiple 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 | tier |
|---|---|---|---|---|
| 1 | Ada | Oslo | 120 | Gold |
| 3 | Sam | Lima | Silver | |
| 2 | Lin | Rome | 75 | |
| 4 | 40 |
- Look at the preview. It has four rows, one for each id. Gaps are NULL: Sam (id 3) has no amount, Lin (id 2) has no tier and id 4 has only an amount.
- Change Join Type to
Inner. Only Ada (id 1) remains, because only her id appears in all three tables.
When to use
Use Join Multiple when two or more tables share the same key name, such as id. It's most useful for three or more tables at once. If key names differ, rename them first with Select. To join two tables with different key names, or to keep unmatched rows separately, use Join. A key that repeats in the inputs produces one row for each combination.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts multiple connections on this port.
Outputs
1 port- Out
Configure the block
- Join Keys
Columns to join on. Each key must have the same name in every input; per-input renaming isn't possible through a multi input, so align names upstream with Select. Keys are picked from the first connected input.
- Join Type
Full outer keeps every row from every input, filling missing values with null. Inner keeps only key values present in all inputs.
Default:
Full outerOptions: Full outer, Inner.
Join Multiple FAQs
- Do the keys need the same name in every table?
- Yes. Rename them first with Select if they differ, or use Join for two tables with different key names.
- What is the difference between Full outer and Inner?
- Full outer keeps every row and fills gaps with NULL. Inner keeps only keys found in all tables.
- What happens to other columns with the same name?
- The key appears once. Other column names found in more than one table get the input number added, such as val_1 and val_2.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.