Skip to main content

LOOKUP

Combine datasets using left outer join logic - all original rows are preserved.

Syntax​

LOOKUP fromClause ON expression

Parameters​

  • fromClause - Either a dataset identifier or an alias assignment (alias = dataset)
  • expression - Boolean condition defining how rows should match

Description​

The LOOKUP command performs a left outer join operation, combining the current dataset with another dataset based on matching conditions you specify in the ON clause. All rows from the original dataset are preserved in the results, regardless of whether they have matches in the lookup dataset.

For rows without matches, the looked-up data struct is set to null. For rows with matches, the looked-up data is nested as a struct to prevent field name collisions. By default, the struct uses the name of the lookup dataset, but you can override this using the assignment syntax in the fromClause.

The lookup condition expression can reference fields in the current dataset directly and fields in the lookup dataset by name using dot notation (e.g., users.email). Use ON true when you need a cartesian product.

  • JOIN - Inner join that only keeps rows with matches