OPAL Join Verbs

Join verbs link multiple datasets together. They may generate more, fewer, or the same number of output rows as input rows, depending on the join condition and verb.

Join cost considerations

Joins are a powerful tool that can increase the amount of data collected and credits consumed. In order to keep your queries efficient, it is useful to check that the join being used is the most efficient one that will work for your use case.

From most expensive to least expensive:

  • lookup or leftjoin between two Resource or Interval datasets

    • This uses an interval overlapping band-join and an additional temporal left outer join. Prefer join to lookup in this case.

  • join between two Resource or Interval datasets

    • This only uses an interval overlapping band-join

  • lookup, leftjoin, or join between one Event and one Resource or Interval dataset (with or without window functions)

  • join or leftjoin between two Event datasets with window functions

    • These all use a point-in-interval band-join

  • lookup, leftjoin, or join between two Event datasets (without window functions), or between one Table and another dataset.

    • These use simple relational joins

Verb

Description

exists

Return the rows from the default dataset that have a match anywhere in the query time window.

follow

Return the rows from the additional joined dataset that have a match anywhere in the query time window.

follow_not

Return the rows from the joined dataset that do not have a match anywhere in the query time window.

fulljoin

Temporal full join, adding new columns in the output dataset.

join

Temporal inner join, adding new columns in the output dataset.

leftjoin

Temporal left join, adding new columns in the output dataset.

lookup

Find matching rows in a dataset, retrieving fields and adding them to the output dataset as new columns.

lookup_ip_info

Using a value or column of type IPv4, look up corresponding fields associated with the IP address.

not_exists

Return the rows from the default dataset that do not have a match anywhere in the query time window.

surrounding

Outer joins datasets by matching row time to a specified frame as well as column values.

union

Create a new dataset consisting of the rows from the main input and each of the arguments.

update_resource

Augments the input Resource dataset using events from another dataset.