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. 表示为
$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 theand
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.
RightColumnwhere
$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
,right
auto
,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
,right
auto
,left
,local
,right
hint.strategy
执行提示Execution hints 请参阅联接提示See Join hints
警告
如果未指定 kind
,则默认的联接风格为 innerunique
。If 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=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=fullouter kind=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=leftantisemi kind=leftanti , kind=leftantisemi |
返回左侧中在右侧没有匹配项的所有记录Returns all the records from the left side that don't have matches from the right |
kind=rightanti , kind=rightantisemi kind=rightanti , kind=rightantisemi |
返回右侧中在左侧没有匹配项的所有记录。Returns all the records from the right side that don't have matches from the left. |
kind 未指定,kind=innerunique kind 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=rightouter 或 kind=fullouter )kind=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 |
leftanti 、anti 或 leftantisemi leftanti , anti , or leftantisemi |
左反联Left anti join |
rightanti 或 rightantisemi rightanti 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 Key
Given 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 thejoin
, 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 filterwhere 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:
hint.shufflekey=<key>
和hint.strategy=shuffle
- 随机执行查询hint.shufflekey=<key>
andhint.strategy=shuffle
- shuffle queryhint.strategy=broadcast
- 广播联接hint.strategy=broadcast
- broadcast joinhint.remote=<strategy>
- 跨群集联接hint.remote=<strategy>
- cross-cluster join