join - 不同类型的内连接及其名称

Question

我有两个简单的数据集：

人物数据集

ID Name Place

1 Person1 City1
2 Person2 City2
4 Person4 City3

设备数据集：

ID Equipment

1 Computer
1 Screen
2 Computer
3 Computer

现在我想结合这些数据集得到以下结果：

ID Name Place Equipment

1 Person1 City1 Computer
1 Person1 City1 Screen
2 Person2 City2 Computer

据我所知，这是一个内部连接。

类型 1

如果我在 Excel 中使用 PowerQuery 来完成这项工作（选择内部联接）：

我可以看到：

当我添加以显示第二个表中的信息时：

它看起来像这样：

最终结果如下：

正如我所料。

我将此连接命名为“内部连接”。

类型 2

现在我有一个可以执行数据操作的软件，我使用它并使用“仅加入匹配项”选项加入数据集。从我的角度来看，它应该加入匹配项，所以 ID 为 1 和 2 的人（具体来说，加入第二列中的 2 行，即计算机和屏幕）。但是，结果如下：

我很惊讶，没想到会这样。我现在的两个问题是：

1.)

这些连接如何调用？我将类型 1 称为“内部连接”，但我不知道如何调用类型 2？还有“内联”？是否存在解释这种不同行为的特定类型的不同内部连接？所以就像完全内连接和严格内连接（作为例子）。

2.)

第二个例子的结果是不是有点误导，“不专业”？我有一个大型数据集，没有收到警告，也没有立即看到。

标签： joinpowerquery

Answer 1

“只参加比赛”

我们不知道该工具，但其行为似乎类似于查找操作。它将表一作为驱动表并尝试在第二个表中找到相应的行/值。

Microsoft SSIS 中也有类似的操作：Lookup Transformation

查找转换通过将输入列中的数据与参考数据集中的列连接来执行查找。您可以使用查找来访问相关表中基于公共列中的值的附加信息。

...

查找转换尝试在转换输入中的值和参考数据集中的值之间执行等连接。（等连接表示转换输入中的每一行必须与参考数据集中的至少一行匹配。）如果等连接不可行，则查找转换将采取以下操作之一

如果参考数据集中没有匹配的条目，则不会发生连接。默认情况下，查找转换将没有匹配条目的行视为错误。但是，您可以配置查找转换以将此类行重定向到不匹配的输出。

如果引用表中有多个匹配项，则查找转换仅返回查找查询返回的第一个匹配项。

如果找到多个匹配项，则仅当已将转换配置为将所有参考数据集加载到缓存中时，查找转换才会生成错误或警告。在这种情况下，当转换在填充缓存时检测到多个匹配项时，查找转换会生成警告。

---

SQL Server 等效项是 CROSS APPLY/LATERAL JOIN 和相关子查询：

SELECT *
FROM Person p
CROSS APPLY (SELECT TOP 1 * FROM Equipment e WHERE p.id = e.Id) sub;
-- please note the first row is random without explicit ORDER BY

db<>小提琴演示

当直接在SELECT列表中用作相关子查询时，它具有以下属性：

1. 子查询必须返回具有零值或一个值的单列
2. 如果子查询返回多于一行，则整个语句将出错
3. 它将返回主表中的所有行，即使没有匹配

样本：

SELECT *, (SELECT  Equipment FROM Equipment e WHERE p.id = e.Id) AS Equipment
FROM Person p;
-- error

SELECT *, (SELECT TOP 1 Equipment FROM Equipment e WHERE p.id = e.Id) AS Equipment
FROM Person p;