SQL INNER JOIN和LEFT JOIN带WHERE的区别

Question

我希望能更直观地理解SQL连接。例如，通过学习如何将RIGHT JOIN重写为LEFT JOIN（通过翻转表的顺序），我更好地理解了这两个连接的工作方式。

然而，现在我想知道是否可以使用WHERE条件将INNER JOIN重写为LEFT JOIN- 这意味着它们的逻辑可以等效（“逻辑”指的不是执行计划，而是描述预期结果集的方式）。

例如：

SELECT * FROM HeaderTable
INNER JOIN DetailTable 
ON HeaderTable.ID = DetailTable.ParentID

我会将其理解为“显示表格HeaderTable和DetailTable中所有具有匹配值的记录，这些匹配值分别位于HeaderTable.ID和DetailTable.ParentID字段。”与之等同的是：

SELECT * FROM HeaderTable
LEFT JOIN DetailTable 
ON    HeaderTable.ID = DetailTable.ParentID
WHERE HeaderTable.ID = DetailTable.ParentID

我会把它读成“展示表HeaderTable和DetailTable中所有记录，其中HeaderTable.ID的值与DetailTable.ParentID的值相同。”

这两个查询语句返回的结果集是否相同？我更多的是询问逻辑是否一致，而不是哪一个比另一个更有效率。

如果可以的话，请不要用任何维恩图回答，因为它们似乎不能准确描述连接的逻辑。

- elmer007

---

一个可视化的表示怎么可能没有帮助呢？是的，在你的例子中它们是相同的，因为当你在where子句中添加谓词时，你只返回那些在两个表中都有匹配的行。 - Sean Lange

你也可以使用“WHERE DetailTable.ParentID IS NOT NULL”，意思是相同的。 - GendoIkari

@SeanLange join操作可能会在结果集中重复数据，我认为这种情况无法很好地用维恩图表示，仅代表个人看法。 - elmer007

没有数据会被重复。当第二个表中有多行与连接谓词匹配时，第一张表的值会出现在这些行上，因为这是唯一合乎逻辑的事情。直到你真正理解它，这可能有点令人困惑。 - Sean Lange

@SeanLange 这很有道理，但我认为这并不是 Venn 图所显示的确切内容，就我个人而言。当我说“第一个表中的值出现在两行中”时，我的意思是重复数据-这是可以接受和有用的，但我认为 Venn 图并没有特别传达这种可能性。这只是我非常挑剔的观点，但仍然只是一种观点。 - elmer007

Answer 1

是的，它们将返回相同的结果。没有where子句的左连接将被解释为“显示头表中的所有记录和与详细表相关联的项目或详细表中没有匹配项的null值”。

添加关于id的where子句相当于通过消除非匹配行来将左连接转换为内部连接，这些行会显示为空白以表示缺少详细信息。

在某些数据库中（如MS SQL Server），查询执行计划中的左连接将显示为内部连接。

尽管您已经声明不需要Venn图，但我还是要提及此问题及其答案，因为它们充满了（在我看来非常有帮助的）Venn图。

- jpw

---

@JeffreyVanLaethem - 你为什么在用 WHERE IS NULL？这完全不是问题的点。这执行的是反半连接，就像 NOT EXISTS。 - Martin Smith

关于连接和连接之间的差异的 Venn 图，请参见我在问题评论中以及您自己链接的 Venn 问答中的问题和答案 - 至少目前为止，我们已经删除了大部分（伪）Venn 图，因为它们被错误标记、误导性和无用。 - philipxy

Answer 2

是的，它们会返回相同的结果。

但是你也可以简单地写成：

SELECT * 
FROM HeaderTable, DetailTable
WHERE HeaderTable.ID = DetailTable.ParentID

这将返回相同的结果。这是在引入连接子句之前使用的旧语法。

- Olivier Jacot-Descombes

Answer 3

在左连接中，如果你在where语句中引用了左表，那么你就会取消左连接的效果，将其转换为普通连接。

- paparazzo

---

在where子句中引用左表是可以的，但如果引用右表，则会将其转换为内连接（假设引用是消除null的内容）。 - Martin Smith

@MartinSmith 当它们都被引用时，在LEFT JOIN DetailTable中，我将DetailTable称为左连接，因为在该语句中是这样的。我知道从逻辑上讲也可以反过来。 - paparazzo

你的术语使用错误。左表是在从左到右读取代码时显示在左侧的那个表。这已经被广泛确认。例如，在左外连接中引用的所有行都来自于左侧的表，而在右外连接中引用的所有行都来自于右侧的表。 - Martin Smith