join 运算符join operator

• 04/07/2021
• • o
  • a
  • v

通过匹配每个表中指定列的值，合并两个表的行以组成新表。Merge the rows of two tables to form a new table by matching values of the specified columns 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

  备注

  如果使用“基于值的等式”，则列名称必须通过由 $left 和 $right 表示法表示的相应的所有者表进行限定。For 'equality by value', the column names must be qualified with the applicable owner table denoted by $left and $right notations.

• 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:

  参数名称Parameters name	值Values	说明Description
  kind	联接风格Join flavors	请参阅联接风格See Join Flavors
  hint.remote	auto, left, local, rightauto, left, local, right	请参阅跨群集联接See Cross-Cluster Join
  hint.strategy	执行提示Execution hints	请参阅联接提示See Join hints

  名称Name	值Values	说明Description
  kind	联接风格Join flavors	请参阅联接风格See Join Flavors
  hint.remote	auto, left, local, rightauto, left, local, right
  hint.strategy	执行提示Execution hints	请参阅联接提示See Join hints

返回Returns

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

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

示例Example

从 login 中获取扩展活动，某些条目将其标记为活动的开始和结束。Get extended activities from a login that some entries mark as 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

联接风格Join flavors

join 运算符的确切风格是通过 kind 关键字指定的。The exact flavor of the join operator is specified with the kind keyword. 支持 join 运算符的以下风格：The following flavors of the join operator are supported:

默认联接风格Default join flavor

默认联接风格是在左侧删除了重复数据的内联。The default join flavor is an inner join with left side deduplication. 在典型的日志/跟踪分析方案中，默认联接实现非常有用。在这种方案中，你想要关联两个事件，每个事件都在同一个相关 ID 下匹配某个筛选条件。Default join implementation is useful in typical log/trace analysis scenarios where you want to correlate two events, each matching some filtering criterion, under the same correlation ID. 你需要获取所有出现的现象，忽略多次出现的构成跟踪记录。You want to get back all appearances of the phenomenon, and ignore multiple appearances of the contributing trace records.

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

下面的两个示例表用来说明联接操作。The following two sample tables are used to explain the operation of the join.

表 XTable X

表 YTable Y

默认联接在左侧对联接键执行重复数据删除后（删除重复数据时会保留第一个记录）执行内联。The default join does an inner join after deduplicating the left side on the join key (deduplication keeps the first record).

对于以下语句：X | join Y on KeyGiven this statement: X | join Y on Key

联接的有效左侧（删除重复数据后的表 X）将是：the effective left side of the join, table X after deduplication, 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

内联风格Inner-join flavor

内联函数类似于 SQL 中的标准内联。The inner-join function is like the standard inner-join from the SQL world. 只要左侧的记录具有与右侧记录相同的联接键，就会生成输出记录。An 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

Innerunique 联接风格Innerunique-join flavor

使用 innerunique 联接风格 从左侧删除重复键。Use innerunique-join flavor to deduplicate keys from the left side. 结果将是进行了重复数据删除的左键和右键的每种组合在输出中存在一行。The result will be a row in the output from every combination of deduplicated left keys and right 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, the flavor used is innerunique and the filter is propagated to the left side of the join. 风格会自动传播，应用于该筛选器的键会始终出现在输出中。The flavor will be automatically propagated and the keys that apply to that filter will always appear in the output.

• 使用上面的示例，添加筛选器 where value == "val1.2" 。Use the example above and add a filter where value == "val1.2" . 它将始终提供第二个结果，永远不会为数据集提供第一个结果：It will always give the second result and will never give the first result for the 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"

左外部联接风格Left outer-join flavor

表 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 doesn't 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 flavor

右外部联接风格与左外部联接类似，但对表的处理是相反的。The right outer-join flavor 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 flavor

完全外部联接合并了应用左外部联接和右外部联接的效果。A full outer-join combines the effect of applying both left and right outer-joins. 如果联接的表中的记录不匹配，则对于表中缺少匹配行的每个列，结果集都会有 null 值。Whenever 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 flavor

左反联接返回左侧中在右侧没有任何匹配记录的所有记录。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

右反联接风格Right anti-join flavor

右反联接返回右侧中在左侧没有任何匹配记录的所有记录。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

左半联接风格Left semi-join flavor

左半联接返回左侧中在右侧具有匹配记录的所有记录。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 flavor

右半联接返回右侧中在左侧具有匹配记录的所有记录。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 本身不提供交叉联接风格。Kusto doesn't natively provide a cross-join flavor. 无法用 kind=cross 来标记运算符。You can't mark the operator with the kind=cross. 若要进行模拟，请使用虚拟键。To simulate, use 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 runs. 这些提示不会更改 join 的语义，但可能会影响其性能。These hints don't change the semantic of join, but may affect its performance.

以下文章解释了联接提示：Join hints are 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