Outer join

Supported in: Batch

Outer joins the provided dataset inputs together, keeping all rows from both datasets. Columns have nulls when there is no row satisfying the provided condition.

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>

Examples

Example 1: Base case

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

ri.foundry.main.dataset.right

Output:

---

Example 2: Base case

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

ri.foundry.main.dataset.right

Output:

---

Example 3: Base case

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

ri.foundry.main.dataset.right

Output:

---

Example 4: Base case

Description: Note, values from the left and right of tail_number are coalesced. 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

ri.foundry.main.dataset.right

Output:

---