Splunk® Cloud Services

SPL2 Search Reference

Acrobat logo Download manual as PDF


Acrobat logo Download topic as PDF

join command usage

The join command is a centralized streaming command, which means that rows are processed one by one. If you are joining two large datasets, the join command can consume a lot of resources.

For flexibility and performance, consider using one of the following commands if you do not require join semantics:

  • lookup command. Use this command when one of the datasets remains static or rarely changes. You can use KV store lookups with SPL2. For example, a file from an external system such as a CSV file.
  • search command. In the most simple scenarios, you might need to search only for sources using the OR operator and then use the stats command to perform the grouping operation on the events.
  • stats command. Use to group events by a field and perform a statistical function on the events. For example to determine the average duration of events by host name. To use stats, the field must have a unique identifier.


The simplest join possible looks like this:

<source> | join left=L right=R 
where L.pid = R.pid [<right-dataset>]

This joins the source, or left-hand side dataset, with the right-hand side dataset. Rows from each dataset are merged into a single row if the where predicate is satisfied.

If you're familiar with SQL, the above example is shorthand for this:

<left-hand side dataset> | join left=L right=R 
type=inner max=1 
where L.pid = R.pid <right-hand side dataset>

One-to-many and many-to-many relationships

To return matches for one-to-many, many-to-one, or many-to-many relationships, include the max argument in your join syntax and set the value to 0. By default max=1, which means that the <dataset> returns only the first result from the <dataset>. Setting the value to a higher number or to 0, which is unlimited, returns multiple results from the <dataset>.

Differences between SPL and SPL2

There are significant differences in the join command between SPL and SPL2.

The SPL2 join command performs very much like a SQL join and has similar syntax to a SQL join. With SPL you are actively encouraged to use other commands instead of the join command because in SPL the join command does not perform like a SQL join.

With SPL 2, the only arguments in the syntax that are not required are the <join-options>. Some of the SPL <join-options> are not supported in SPL2. Specifically the usetime, earlier, and overwrite join options are not supported.

SPL2 uses a very different syntax

The syntax for the join command is completely different. You must specify field aliases. Field names are required.

Version Syntax
SPL [join-options...][field-list] subsearch
SPL2 join

(<join-options>...)
left=<left-alias>
right=<right-alias>
where <left-alias>.<left-field>=<right-alias>.<right-field>
[ AND <left-alias>.<left-field>=<right-alias>.<right-field> ]...
<right dataset>


Field names to join on can be different

Field names do not have to be renamed so that you can join on the key fields. This example joins the incoming search results with the products dataset.

Version Example
SPL rename vid AS vendor_id]
SPL2 ... join left=L right=R where L.vendorID=R.vid products

See also

join command
join command overview
join command syntax details
join command examples
Last modified on 20 October, 2020
PREVIOUS
join command syntax details
  NEXT
join command examples

This documentation applies to the following versions of Splunk® Cloud Services: current


Was this documentation topic helpful?

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters