Inner join

Supported in: Batch

Joins two datasets together, keeping only rows that satisfy the provided condition from each table.

Transform categories: Join

Declared arguments

Condition for columns to select on the left - All columns in the left input schema will be tested to see if they match this condition. If they match, the column will be selected in the output.
ColumnPredicate
Condition for columns to select on the right - All columns in the right input schema will be tested to see if they match this condition. If they match, the column will be selected in the output.
ColumnPredicate
Join condition - Condition on which to join.
Expression<Boolean>
Left dataset - Left dataset to use in join.
Table
Right dataset - Right dataset to use in join.
Table
optional Prefix for columns from right - Prefix to add to all columns on the right hand side.
Literal<String>

Argument values:

Condition for columns to select on the left:
columnNameIsIn(
columnNames: [tail_number, airline],
)
Condition for columns to select on the right:
columnNameIsIn(
columnNames: [home_airport],
)
Join condition:
equals(
left: tail_number,
right: tail_number,
)
Left dataset: ri.foundry.main.dataset.left
Right dataset: ri.foundry.main.dataset.right
Prefix for columns from right: null

Inputs: ri.foundry.main.dataset.left

tail_number	airline	miles	factor
XB-123	foundry air	124	2
MT-222	new airline	1123	5
XB-123	foundry airline	335	5
MT-222	new air	565	4
KK-452	new air	222	1
PA-452	new air	212	2
XB-123	foundry airline	1134	2

ri.foundry.main.dataset.right

Output:

Description: Simple complex join condition. Argument values:

Condition for columns to select on the left:
columnNameIsIn(
columnNames: [tail_number, airline, factor],
)
Condition for columns to select on the right:
columnNameIsIn(
columnNames: [tail_number, home_airport, factor],
)
Join condition:
lessThan(
left: factor,
right: factor,
)
Left dataset: ri.foundry.main.dataset.left
Right dataset: ri.foundry.main.dataset.right
Prefix for columns from right: right_

Inputs: ri.foundry.main.dataset.left

tail_number	airline	miles	factor
XB-123	foundry air	124	2
MT-222	new airline	1123	5
XB-123	foundry airline	335	5
MT-222	new air	565	4
KK-452	new air	222	1
PA-452	new air	212	2
XB-123	foundry airline	1134	2

ri.foundry.main.dataset.right

Output:

tail_number	airline	factor	right_tail_number	right_home_airport	right_factor
XB-123	foundry air	2	KK-452	JFK	10
XB-123	foundry air	2	JR-201	IAD	4
MT-222	new airline	5	KK-452	JFK	10
XB-123	foundry airline	5	KK-452	JFK	10
MT-222	new air	4	KK-452	JFK	10
KK-452	new air	1	XB-123	LHR	2
KK-452	new air	1	KK-452	JFK	10
KK-452	new air	1	JR-201	IAD	4
PA-452	new air	2	KK-452	JFK	10
PA-452	new air	2	JR-201	IAD	4
XB-123	foundry airline	2	KK-452	JFK	10
XB-123	foundry airline	2	JR-201	IAD	4

Argument values:

Condition for columns to select on the left:
columnNameIsIn(
columnNames: [tail_number, airline, factor],
)
Condition for columns to select on the right:
columnNameIsIn(
columnNames: [home_airport],
)
Join condition:
and(
conditions: [
equals(
left: tail_number,
right: tail_number,
),
equals(
left: factor,
right: factor,
)],
)
Left dataset: ri.foundry.main.dataset.left
Right dataset: ri.foundry.main.dataset.right
Prefix for columns from right: null

Inputs: ri.foundry.main.dataset.left

tail_number	airline	miles	factor
XB-123	foundry air	124	2
MT-222	new airline	1123	5
XB-123	foundry airline	335	5
MT-222	new air	565	4
KK-452	new air	222	1
PA-452	new air	212	2
XB-123	foundry airline	1134	2

ri.foundry.main.dataset.right

Output:

tail_number	airline	factor	home_airport
XB-123	foundry air	2	LHR
XB-123	foundry airline	2	LHR

Argument values:

Condition for columns to select on the left:
allColumns(

)
Condition for columns to select on the right:
columnNameIsIn(
columnNames: [home_airport],
)
Join condition:
equals(
left: tail_number,
right: tail_number,
)
Left dataset: ri.foundry.main.dataset.left
Right dataset: ri.foundry.main.dataset.right
Prefix for columns from right: null

Inputs: ri.foundry.main.dataset.left

tail_number	airline	miles	factor
XB-123	foundry air	124	2
MT-222	new airline	1123	5
XB-123	foundry airline	335	5
MT-222	new air	565	4
KK-452	new air	222	1
PA-452	new air	212	2
XB-123	foundry airline	1134	2

ri.foundry.main.dataset.right

Output:

tail_number	airline	miles	factor	home_airport
XB-123	foundry air	124	2	LHR
MT-222	new airline	1123	5	CPH
XB-123	foundry airline	335	5	LHR
MT-222	new air	565	4	CPH
KK-452	new air	222	1	JFK
XB-123	foundry airline	1134	2	LHR