join command syntax details

The required syntax is in bold.

Syntax

join

(<join-options>...)

left=<left-alias>

right=<right-alias>

where <left-alias>.<left-field>=<right-alias>.<right-field>...

<right-dataset>

left: Syntax: left=<left-alias>; Description: The alias to use with the left-side dataset, the source data, to avoid naming collisions.

right: Syntax: right=<right-alias>; Description: The alias to use with the right-side dataset to avoid naming collisions.

where: Syntax: where <left-alias>.<left-field>=<right-alias>.<right-field> ...; Description: The names of the fields in the left-side dataset and the right-side dataset that you want to join on. You must specify the alias and the field name. For example: L.host=R.user. To join on multiple fields, you must specify AND operator between each set of fields. For example: L.host=R.user AND L.clientip=R.clientip.

You can specify the aliases and fields in where clause on either side of the equal sign.

For example, you can specify:

where <left-alias>.<left-field>=<right-alias>.<right-field>

or

where <right-alias>.<right-field>=<left-alias>.<left-field>

right-dataset: Syntax: <dataset> | [ subsearch ]; Description: The name of the right-side dataset or the subsearch that you want to use to join with the source data. If you specify a dataset, it must be a dataset that you created or are authorized to use. If you specify a subsearch, it must be enclosed in square brackets. A maximum of 50000 rows in the right-side dataset can be joined with the left-side dataset.

join-options: Syntax: [ type | max ]; Description: Specify the type of join to perform and the maximum number of rows to join on. You can specify one or more <join-options>.

type: Syntax: type=<inner | outer | left>; Description: Indicates the type of join to perform. The difference between an inner and a left (or outer) join is how the rows are treated in the left-side dataset that do not match any of the rows in the right-side dataset. In both inner and left joins, rows that match are joined. The results of an inner join do not include rows from the left-side dataset that have no matches in the right-side dataset. The results of a left (or outer) join includes all of the rows in the left-side dataset and only those values in the right-side dataset have matching field values.; Default: inner

max: Syntax: max=<int>; Description: Specifies the maximum number of rows in the right-side dataset that each row in the left-side dataset can join with. The default setting means that 1 row in the right-side dataset can join with just 1 row in the left-side dataset. If set to max=0, multiple rows in the right-side dataset join with 1 row in the left-side dataset.; Default: 1