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.