SQL INNER JOIN与带有WHERE的LEFT JOIN [英] SQL INNER JOIN vs LEFT JOIN with a WHERE

问题描述

我试图更直观地掌握SQL连接.就在昨天，我了解了如何将RIGHT JOIN重新编写为LEFT JOIN(通过翻转表的顺序)，这有助于我更好地理解两个联接的工作方式.

I am trying to grasp SQL joins more intuitively. Just yesterday, I learned about how a RIGHT JOIN can just be re-written as a LEFT JOIN (by flipping the order of the tables), which helped me understand much better the way that the two joins work.

但是，现在我想知道是否可以将WHERE条件下的INNER JOIN重新编写为LEFT JOIN，这意味着它们的逻辑可以等效(逻辑"不是执行计划，而是描述预期结果集的方式).

However, now I'm wondering if an INNER JOIN could be re-written as a LEFT JOIN with a WHERE condition- meaning that their logic could be equivalent (by "logic" I do not mean the execution plan, but the way that the intended result set would be described).

赞:

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

我将其读为向我显示表 HeaderTable 和 DetailTable 中在 HeaderTable.ID 中具有匹配值的所有记录.和 DetailTable.ParentID 字段."与以下相同:

Which I would read as "Show me all the records from tables HeaderTable and DetailTable that have a matching value in the HeaderTable.ID and DetailTable.ParentID fields." Being the same as:

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

我将其读为向我显示表 HeaderTable 和 DetailTable 中的所有记录，其中 HeaderTable.ID 的值相同作为 DetailTable.ParentID 的值."

Which I would read as "Show me all the records from tables HeaderTable and DetailTable where the value of HeaderTable.ID is the same as the value of DetailTable.ParentID."

这些将返回相同的结果集吗?我要问的是逻辑是否相同，而不是一种逻辑比另一种更有效率.

如果我可能要问，请不要回答任何维恩图，因为这些图似乎并不能完全向我描述联接的逻辑.

If I may ask, please don't answer with any Venn diagrams as these don't seem to describe the logic of a join exactly to me.

推荐答案

是的，它们将返回相同的结果.不带where子句的左联接将显示为，向我显示标题表中的所有记录以及明细表中的相关项，或者对没有匹配项的明细为空.

Yes, they will return the same result. The left join without the where clause would read as show me all the records from the header table and the related items from the details table or null for the details where there are no matches.

添加一个与id相关的where子句，可以通过消除不匹配的行将左连接转换为内部连接，这些不匹配的行在细节部分显示为空.

Adding a where clause relating the ids effectively transforms the left join to an inner join by eliminating the non-matching rows that would have shown up as having null for the detail part.

在某些数据库(例如MS SQL Server)中，左联接将在查询执行计划中显示为内部联接.

In some databases, like MS SQL Server, the left join would show up as an inner join in the query execution plan.

尽管您说过您不想要维恩图，但我不由自主地将您引荐给

Although you stated that you don't want Venn diagrams I can't help referring you to this question and its answers even though they are filled with (in my opinion very helpful) Venn diagrams.

这篇关于SQL INNER JOIN与带有WHERE的LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！