join 运算符join operator

• 08/06/2020
• • 
  • 

通过匹配每个表中指定列的值，合并两个表的行以组成新表。Merge the rows of two tables to form a new table by matching values of the specified column(s) from each table.

Table1 | join (Table2) on CommonColumn, $left.Col1 == $right.Col2

语法Syntax

LeftTable | join [JoinParameters] ( RightTable ) on AttributesLeftTable | join [JoinParameters] ( RightTable ) on Attributes

参数Arguments

• LeftTable：要合并其行的左侧表或表格表达式（有时称为外部表。LeftTable: The left table or tabular expression (sometimes called outer table) whose rows are to be merged. 表示为 $left。Denoted as $left.

• RightTable：要合并其行的右侧表或表格表达式（有时称为*内部表。RightTable: The right table or tabular expression (sometimes called *inner table) whose rows are to be merged. 表示为 $right。Denoted as $right.

• Attributes：一个或多个（逗号分隔的）规则，这些规则描述 LeftTable 中的行如何与 RightTable 中的行进行匹配。Attributes: One or more (comma-separated) rules that describe how rows from LeftTable are matched to rows from RightTable. 将使用 and 逻辑运算符评估多个规则。Multiple rules are evaluated using the and logical operator. 规则可以是下列项之一：A rule can be one of:

  规则类型Rule kind	语法Syntax	PredicatePredicate
  基于名称的等式Equality by name	ColumnNameColumnName	where LeftTable.ColumnName == RightTable.ColumnNamewhere LeftTable.ColumnName == RightTable.ColumnName
  基于值的等式Equality by value	$left.LeftColumn == $right.RightColumn$left.LeftColumn == $right.RightColumn	where $left.LeftColumn == $right.RightColumnwhere $left.LeftColumn == $right.RightColumn

• JoinParameters：零个或零个以上（以空格分隔）以 Name = Value 形式表示的参数，用于控制行匹配操作和执行计划的行为 。JoinParameters: Zero or more (space-separated) parameters in the form of Name = Value that control the behavior of the row-match operation and execution plan. 支持以下参数：The following parameters are supported:

返回Returns

输出架构取决于联接风格：Output schema depends on the join flavor:

• kind=leftanti, kind=leftsemi:kind=leftanti, kind=leftsemi:

  结果表中只包含来自左侧的列。The result table contains columns from the left side only.

• kind=rightanti, kind=rightsemi:kind=rightanti, kind=rightsemi:

  结果表中只包含来自右侧的列。The result table contains columns from the right side only.

• kind=innerunique, kind=inner, kind=leftouter, kind=rightouter, kind=fullouterkind=innerunique, kind=inner, kind=leftouter, kind=rightouter, kind=fullouter

  一列，用于每个表中的每一列，包括匹配键。A column for every column in each of the two tables, including the matching keys. 如果存在名称冲突，会自动重命名右侧列。The columns of the right side will be automatically renamed if there are name clashes.

输出记录取决于联接风格：Output records depends on the join flavor:

• kind=leftanti, kind=leftantisemikind=leftanti, kind=leftantisemi

  返回左侧中在右侧没有匹配项的所有记录。Returns all the records from the left side that don't have matches from the right.

• kind=rightanti, kind=rightantisemikind=rightanti, kind=rightantisemi

  返回右侧中在左侧没有匹配项的所有记录。Returns all the records from the right side that don't have matches from the left.

• kind=innerunique, kind=inner, kind=leftouter, kind=rightouter, kind=fullouter, kind=leftsemi, kind=rightsemikind=innerunique, kind=inner, kind=leftouter, kind=rightouter, kind=fullouter, kind=leftsemi, kind=rightsemi

  一行，用于输入表之间的所有匹配项。A row for every match between the input tables. 匹配项是从一个表中选出的一行，该行中所有 on 字段的值与另一个表中某行的对应值相同，具有以下约束：A match is a row selected from one table that has the same value for all the on fields as a row in the other table with these constraints:

  • kind 未指定，kind=inneruniquekind unspecified, kind=innerunique

  左侧中仅有一行与 on 键的每个值匹配。Only one row from the left side is matched for each value of the on key. 输出包含一行，用于此行与右侧行的每一个匹配项。The output contains a row for each match of this row with rows from the right.

  • kind=leftsemi

  返回左侧中在右侧具有匹配项的所有记录。Returns all the records from the left side that have matches from the right.

  • kind=rightsemi

    返回右侧中在左侧具有匹配项的所有记录。Returns all the records from the right side that have matches from the left.

  • kind=inner

  输出中存在一行，用于左侧和右侧匹配行的每个组合。There's a row in the output for every combination of matching rows from left and right.

  • kind=leftouter（或 kind=rightouter 或 kind=fullouter）kind=leftouter (or kind=rightouter or kind=fullouter)

  除了内部匹配，还有一行用于左侧（和/或右侧）的每一行（即使没有匹配项）。In addition to the inner matches, there's a row for every row on the left (and/or right), even if it has no match. 在这种情况下，不匹配的输出单元格包含 null。In that case, the unmatched output cells contain nulls. 如果这些字段有多个行具有相同的值，则会获得所有合并的行。If there are several rows with the same values for those fields, you'll get rows for all the combinations.

提示Tips

为获得最佳性能：For best performance:

• 如果某个表始终小于另一个表，则将其用作 join 的左侧（通过管道传送）。If one table is always smaller than the other, use it as the left (piped) side of the join.

示例Example

从日志中获取扩展活动，某些条目在日志中标记活动的开始和结束时间。Get extended activities from a log in which some entries mark the start and end of an activity.

let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityId, StartTime=timestamp
| join (Events
    | where Name == "Stop"
        | project StopTime=timestamp, ActivityId)
    on ActivityId
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime

let Events = MyLogTable | where type=="Event" ;
Events
| where Name == "Start"
| project Name, City, ActivityIdLeft = ActivityId, StartTime=timestamp
| join (Events
        | where Name == "Stop"
        | project StopTime=timestamp, ActivityIdRight = ActivityId)
    on $left.ActivityIdLeft == $right.ActivityIdRight
| project City, ActivityId, StartTime, StopTime, Duration = StopTime - StartTime

有关此示例的更多信息。More about this example.

联接风格Join flavors

联接运算符的确切风格是通过 kind 关键字指定的。The exact flavor of the join operator is specified with the kind keyword. 目前，Kusto 支持联接运算符的以下风格：As of today, Kusto supports the following flavors of the join operator:

inner 和 innerunique 联接运算符风格inner and innerunique join operator flavors

• 使用 inner 联接风格时，对于在左侧和右侧具有匹配行的每个组合（不删除左侧的重复键），输出中都将存在一行。When using inner join flavor, there will be a row in the output for every combination of matching rows from left and right without left keys deduplications. 输出将是左键和右键的笛卡尔积。The output will be a cartesian product of left and right keys. 内联示例：Example of inner join:

let t1 = datatable(key:long, value:string)  
[
1, "val1.1",  
1, "val1.2"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "val1.3", 
1, "val1.4"  
];
t1
| join kind = inner
    t2
on key

• 使用 innerunique 联接风格会删除左侧的重复键，并且对于删除了重复数据的左侧键和右侧键的每个组合，输出中都将存在一行。Using innerunique join flavor will deduplicate keys from left side and there will be a row in the output from every combination of deduplicated left keys and right keys. 上面使用的相同数据集的 innerunique 联接示例。请注意，对于本例，innerunique 风格会产生两个可能的输出，两个都是正确的。Example of innerunique join for the same datasets used above, Please note that innerunique flavor for this case may yield two possible outputs and both are correct. 在第一个输出中，联接运算符随机选取 t1 中出现的值为“val1.1”的第一个键，并将其与 t2 键进行匹配，而在第二个输出中，联接运算符随机选取 t1 中出现的值为“val1.2”的第二个键，并将其与 t2 键进行匹配：In the first output, the join operator randomly picked the first key which appears in t1 with the value "val1.1" and matched it with t2 keys while in the second one, the join operator randomly picked the second key appears in t1 which has the value "val1.2" and matched it with t2 keys:

let t1 = datatable(key:long, value:string)  
[
1, "val1.1",  
1, "val1.2"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "val1.3", 
1, "val1.4"  
];
t1
| join kind = innerunique
    t2
on key

let t1 = datatable(key:long, value:string)  
[
1, "val1.1",  
1, "val1.2"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "val1.3", 
1, "val1.4"  
];
t1
| join kind = innerunique
    t2
on key

• Kusto 已经过优化，它会尽可能将 join 之后的筛选器推向相应的联接左侧或右侧。Kusto is optimized to push filters that come after the join towards the appropriate join side left or right when possible. 有时候，当使用的风格是 innerunique 并且筛选器可以传播到联接的左侧时，它会自动传播，并且应用于该筛选器的键会始终显示在输出中。Sometimes, when the flavor used is innerunique and the filter can be propagated to the left side of the join, then it will be propagated automatically and the keys which applies to that filter will always appear in the output. 例如，使用上面的示例并添加筛选器 where value == "val1.2" 会始终生成第二个结果，并且永远不会为使用的数据集生成第一个结果：for example, using the example above and adding filter where value == "val1.2" will always give the second result and will never give the first result for the used datasets :

let t1 = datatable(key:long, value:string)  
[
1, "val1.1",  
1, "val1.2"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "val1.3", 
1, "val1.4"  
];
t1
| join kind = innerunique
    t2
on key
| where value == "val1.2"

默认联接风格Default join flavor

X | join Y on Key
X | join kind=innerunique Y on Key
 

让我们使用两个示例表来说明联接操作：Let's use two sample tables to explain the operation of the join:

表 XTable X

表 YTable Y

默认联接在左侧对联接键执行重复数据删除后（删除重复数据时会保留第一个记录）执行内联。The default join performs an inner join after de-duplicating the left side on the join key (deduplication retains the first record). 对于以下语句：Given this statement:

X | join Y on Key 

联接的有效左侧（删除重复数据后的表 X）将是：the effective left side of the join (table X after de-duplication) would be:

联接结果将是：and the result of the join would be:

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join Y on Key

（注意，键“a”和“d”没有出现在输出中，因为在左侧和右侧都没有匹配的键）。(Note that the keys 'a' and 'd' don't appear in the output, since there were no matching keys on both left and right sides).

（历史上，这是 Kusto 的初始版本所支持的联接的第一个实现；这在典型的日志/跟踪分析场景中非常有用。在此类场景中，我们需要将使用同一相关 ID 的两个事件（每个都与某个筛选条件匹配）相关联，获取所有出现的现象（这正是我们要找的），同时忽略多次出现的构成跟踪记录。）(Historically, this was the first implementation of the join supported by the initial version of Kusto; it is useful in the typical log/trace analysis scenarios where we want to correlate two events (each matching some filtering criterion) under the same correlation ID, and get back all appearances of the phenomenon we're looking for, ignoring multiple appearances of the contributing trace records.)

内部联接Inner join

这是 SQL 中已知的标准内联。This is the standard inner join as known from the SQL world. 只要左侧的记录具有与右侧记录相同的联接键，就会生成输出记录。Output record is produced whenever a record on the left side has the same join key as the record on the right side.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=inner Y on Key

请注意，来自右侧的 (b,10) 联接了两次：与左侧的 (b,2) 和 (b,3) 都进行了联接；此外，左侧的 (c,4) 联接了两次：与右侧的 (c,20) 和 (c,30) 都进行了联接。Note that (b,10) coming from the right side was joined twice: with both (b,2) and (b,3) on the left; also (c,4) on the left was joined twice: with both (c,20) and (c,30) on the right.

左外部联接Left outer join

表 X 和 Y 的左外联的结果始终包含左表 (X) 的所有记录，即使联接条件在右表 (Y) 中未找到任何匹配记录。The result of a left outer join for tables X and Y always contains all records of the left table (X), even if the join condition does not find any matching record in the right table (Y).

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftouter Y on Key

右外部联接Right outer join

与左外联类似，但对表的处理是相反的。Resembles the left outer join, but the treatment of the tables is reversed.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightouter Y on Key

完全外联Full outer join

从概念上讲，完全外联合并了应用左外联和右外联的效果。Conceptually, a full outer join combines the effect of applying both left and right outer joins. 如果联接的表中的记录不匹配，则对于表中缺少匹配行的每个列，结果集都会有 NULL 值。Where records in the joined tables don't match, the result set will have NULL values for every column of the table that lacks a matching row. 对于那些匹配的记录，结果集中会生成单个行（其中包含从两个表中填充的字段）。For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=fullouter Y on Key

左反联Left anti join

左反联返回左侧中在右侧没有任何匹配记录的所有记录。Left anti join returns all records from the left side that don't match any record from the right side.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftanti Y on Key

反联模拟“NOT IN”查询。Anti-join models the "NOT IN" query.

右反联Right anti join

右反联返回右侧中在左侧没有任何匹配记录的所有记录。Right anti join returns all records from the right side that don't match any record from the left side.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightanti Y on Key

反联模拟“NOT IN”查询。Anti-join models the "NOT IN" query.

左半联Left semi join

左半联返回左侧中在右侧具有匹配记录的所有记录。Left semi join returns all records from the left side that match a record from the right side. 仅会返回左侧的列。Only columns from the left side are returned.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=leftsemi Y on Key

右半联Right semi join

右半联返回右侧中在左侧具有匹配记录的所有记录。Right semi join returns all records from the right side that match a record from the left side. 仅会返回右侧的列。Only columns from the right side are returned.

let X = datatable(Key:string, Value1:long)
[
    'a',1,
    'b',2,
    'b',3,
    'c',4
];
let Y = datatable(Key:string, Value2:long)
[
    'b',10,
    'c',20,
    'c',30,
    'd',40
];
X | join kind=rightsemi Y on Key

交叉联接Cross join

Kusto 本身不支持交叉联接风格（也就是说，你无法使用 kind=cross 标记此运算符）。Kusto doesn't natively provide a cross-join flavor (i.e., you can't mark the operator with kind=cross). 不过，不难通过虚拟键来模拟这一风格：It isn't difficult to simulate this, however, by coming up with a dummy key:

X | extend dummy=1 | join kind=inner (Y | extend dummy=1) on dummy

联接提示Join hints

join 运算符支持许多用于控制查询执行方式的提示。The join operator supports a number of hints that control the way a query executes. 这些提示不会更改 join 的语义，但可能会影响其性能。These do not change the semantic of join, but may affect its performance.

以下文章解释了联接提示：Join hints explained in the following articles:

• hint.shufflekey=<key> 和 hint.strategy=shuffle - 随机执行查询hint.shufflekey=<key> and hint.strategy=shuffle - shuffle query
• hint.strategy=broadcast - 广播联接hint.strategy=broadcast - broadcast join
• hint.remote=<strategy> - 跨群集联接hint.remote=<strategy> - cross-cluster join