join operator
Merge the rows of two tables to form a new table by matching values of the specified columns from each table.
pipelined query language (pql) offers many kinds of joins that each affect the schema and rows in the resultant table in different ways. For example, if you use an inner join, the table has the same columns as the left table, plus the columns from the right table. For best performance, if one table is always smaller than the other, use it as the left side of the join operator. The following image provides a visual representation of the operation performed by each join.
Syntax
LeftTable | join [ kind = JoinFlavor ] [ Hints ] (RightTable) on Conditions
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| LeftTable | string | ✓ | The left table or tabular expression, sometimes called the outer table, whose rows are to be merged. Denoted as $left. |
| JoinFlavor | string | The type of join to perform: innerunique, inner, leftouter. The default is innerunique. | |
| RightTable | string | ✓ | The right table or tabular expression, sometimes called the inner table, whose rows are to be merged. Denoted as $right. |
| Conditions | string | ✓ | Determines how rows from LeftTable are matched with rows from RightTable. If the columns you want to match have the same name in both tables, use the syntax ON ColumnName. Otherwise, use the syntax ON $left.LeftColumn == $right.RightColumn. To specify multiple conditions, you can either use the "and" keyword or separate them with commas. If you use commas, the conditions are evaluated using the "and" logical operator. |
[!TIP] For best performance, if one table is always smaller than the other, use it as the left side of the join.
Returns
The return schema and rows depend on the join flavor. The join flavor is specified with the kind keyword. The following flavors of the join operator are supported:
| Join flavor | Returns |
|---|---|
| innerunique (default) | Inner join with left side deduplication Schema: All columns from both tables, including the matching keys Rows: All deduplicated rows from the left table that match rows from the right table |
| inner | Standard inner join Schema: All columns from both tables, including the matching keys Rows: Only matching rows from both tables |
| leftouter | Left outer join Schema: All columns from both tables, including the matching keys Rows: All records from the left table and only matching rows from the right table |
Cross-join
KQL doesn't provide a cross-join flavor. However, you can achieve a cross-join effect by using a placeholder key approach.
In the following example, a placeholder key is added to both tables and then used for the inner join operation, effectively achieving a cross-join-like behavior:
X | extend placeholder=1 | join kind=inner (Y | extend placeholder=1) on placeholder