join 运算符join operator

通过匹配每个表中指定列的值,合并两个表的行以组成新表。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. 表示为 $leftDenoted as $left.

  • RightTable:要合并其行的 右侧 表或表格表达式(有时称为 内部 表)。RightTable: The right table or tabular expression, sometimes called inner table, whose rows are to be merged. 表示为 $rightDenoted 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

警告

如果未指定 kind,则默认的联接风格为 inneruniqueIf kind isn't specified, the default join flavor is innerunique. 这不同于其他的一些分析产品,这些产品以 inner 作为默认风格。This is different than some other analytics products that have inner as the default flavor. 请参阅联接风格来了解不同之处,确保查询产生预期结果。See join-flavors to understand the differences and make sure the query yields the intended results.

返回Returns

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

联接风格Join flavor 输出架构Output schema
kind=leftanti, kind=leftsemikind=leftanti, kind=leftsemi 结果表中只包含来自左侧的列。The result table contains columns from the left side only.
kind=rightanti, kind=rightsemikind=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 depend on the join flavor:

备注

如果这些字段有多个行具有相同的值,则会获得所有合并的行。If there are several rows with the same values for those fields, you'll get rows for all the combinations. 匹配项是从表中选出的一行,该表中的所有 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.

联接风格Join flavor 输出记录Output records
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 未指定,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 输出中包含一行,该行对应于左右匹配行的每种组合。Contains a row in the output for every combination of matching rows from left and right.
kind=leftouter(或 kind=rightouterkind=fullouterkind=leftouter (or kind=rightouter or kind=fullouter) 包含的一行对应于左侧和右侧的每一行,即使没有匹配项。Contains a row for every row on the left and right, even if it has no match. 不匹配的输出单元格包含 null。The unmatched output cells contain nulls.

提示

为获得最佳性能,如果某个表始终小于另一个表,则将其用作 join 的左侧(管接)。For best performance, if one table is always smaller than the other, use it as the left (piped) side of the join.

示例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:

联接类型/风格Join kind/flavor 描述Description
innerunique(或默认为空)innerunique (or empty as default) 执行左侧重复数据删除的内联Inner join with left side deduplication
inner 标准内联Standard inner join
leftouter 左外部联接Left outer join
rightouter 右外部联接Right outer join
fullouter 完全外联Full outer join
leftantiantileftantisemileftanti, anti, or leftantisemi 左反联Left anti join
rightantirightantisemirightanti or rightantisemi 右反联Right anti join
leftsemi 左半联Left semi join
rightsemi 右半联Right semi join

默认联接风格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

Key Value1Value1
aa 11
bb 22
bb 33
cc 44

表 YTable Y

Key Value2Value2
bb 1010
cc 2020
cc 3030
dd 4040

默认联接在左侧对联接键执行重复数据删除后(删除重复数据时会保留第一个记录)执行内联。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:

Key Value1Value1
aa 11
bb 22
cc 44

联接结果将是: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
Key Value1Value1 Key1Key1 Value2Value2
bb 22 bb 1010
cc 44 cc 2020
cc 44 cc 3030

备注

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

内联风格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
Key Value1Value1 Key1Key1 Value2Value2
bb 33 bb 1010
bb 22 bb 1010
cc 44 cc 2020
cc 44 cc 3030

备注

  • 右侧的 (b,10) 联接了两次:与左侧的 (b,2) 和 (b,3) 都进行了联接。(b,10) from the right side, was joined twice: with both (b,2) and (b,3) on the left.
  • 左侧的 (c,4) 联接了两次:与右侧的 (c,20) 和 (c,30) 都进行了联接。(c,4) on the left side, was joined twice: with both (c,20) and (c,30) on the right.

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.

备注

innerunique 风格 可能产生两个可能的输出,两者都是正确的。innerunique flavor may yield two possible outputs and both are correct. 在第一个输出中,join 运算符随机选择了出现在 t1 中的第一个键,其值为“val1.1”,并将其与 t2 键匹配。In the first output, the join operator randomly selected the first key that appears in t1, with the value "val1.1" and matched it with t2 keys. 在第二个输出中,join 运算符随机选择了出现在 t1 中的第二个键,其值为“val1.2”,并将其与 t2 键匹配。In the second output, the join operator randomly selected the second key that appears in t1, with 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
keykey valuevalue key1key1 value1value1
11 val1.1val1.1 11 val1.3val1.3
11 val1.1val1.1 11 val1.4val1.4
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
keykey valuevalue key1key1 value1value1
11 val1.2val1.2 11 val1.3val1.3
11 val1.2val1.2 11 val1.4val1.4
  • 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"
keykey valuevalue key1key1 value1value1
11 val1.2val1.2 11 val1.3val1.3
11 val1.2val1.2 11 val1.4val1.4

左外部联接风格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
Key Value1Value1 Key1Key1 Value2Value2
bb 33 bb 1010
bb 22 bb 1010
cc 44 cc 2020
cc 44 cc 3030
aa 11

右外部联接风格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
Key Value1Value1 Key1Key1 Value2Value2
bb 33 bb 1010
bb 22 bb 1010
cc 44 cc 2020
cc 44 cc 3030
dd 4040

完全外部联接风格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
Key Value1Value1 Key1Key1 Value2Value2
bb 33 bb 1010
bb 22 bb 1010
cc 44 cc 2020
cc 44 cc 3030
dd 4040
aa 11

左反联接风格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
Key Value1Value1
aa 11

备注

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

右反联接风格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
Key Value2Value2
dd 4040

备注

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

左半联接风格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
Key Value1Value1
bb 33
bb 22
cc 44

右半联接风格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
Key Value2Value2
bb 1010
cc 2020
cc 3030

交叉联接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: