SQL 概念 LEFT OUTER JOIN 和 WHERE NOT EXISTS 是否基本相同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9775693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Are the SQL concepts LEFT OUTER JOIN and WHERE NOT EXISTS basically the same?
提问by Steffan Harris
Whats the difference between using a LEFT OUTER JOIN, rather than a sub-query that starts with a WHERE NOT EXISTS (...)?
使用LEFT OUTER JOIN, 而不是以 开头的子查询有什么区别WHERE NOT EXISTS (...)？
回答by Michael Berkowski
No they are not the same thing, as they will notreturn the same rowset in the most simplistic use case.
不，它们不是一回事，因为在最简单的用例中它们不会返回相同的行集。
The LEFT OUTER JOINwill return all rows from the left table, both where rows exist in the related table and where they does not. The WHERE NOT EXISTS()subquery will only return rows where the relationship is not met.
在LEFT OUTER JOIN将返回左表中的所有行，无论是中行在相关表中存在和他们没有。该WHERE NOT EXISTS()子查询只会返回我们的关系不符合行。
However, if you did a LEFT OUTER JOINand looked for IS NULLon the foreign key column in the WHEREclause, you can make equivalent behavior to the WHERE NOT EXISTS.
但是，如果您在子句中的外键列上执行aLEFT OUTER JOIN和查找，则可以做出与. IS NULLWHEREWHERE NOT EXISTS
For example this:
例如这个：
SELECT
t_main.*
FROM
t_main
LEFT OUTER JOIN t_related ON t_main.id = t_related.id
/* IS NULL in the WHERE clause */
WHERE t_related.id IS NULL
Is equivalent to this:
相当于：
SELECT
t_main.*
FROM t_main
WHERE
NOT EXISTS (
SELECT t_related.id
FROM t_related
WHERE t_main.id = t_related.id
)
But this one is notequivalent:
但这一个不等价：
It will return rows from t_mainboth having and not having related rows in t_related.
它将从 中t_main具有和不具有相关行返回行t_related。
SELECT
t_main.*
FROM
t_main
LEFT OUTER JOIN t_related ON t_main.id = t_related.id
/* WHERE clause does not exclude NULL foreign keys */
NoteThis does not speak to how the queries are compiled and executed, which differs as well -- this only addresses a comparison of the rowsets they return.
注意这并不说明查询是如何编译和执行的，这也有所不同——这仅解决了它们返回的行集的比较。
回答by ntziolis
As Michael already answered your question here is a quick sample to illustrate the difference:
由于迈克尔已经回答了您的问题，这里有一个快速示例来说明差异：
Table A
Key Data
1 somedata1
2 somedata2
Table B
Key Data
1 data1
Left outer join:
左外连接：
SELECT *
FROM A
LEFT OUTER JOIN B
ON A.Key = B.Key
Result:
结果：
Key Data Key Data
1 somedata1 1
2 somedata2 null null
EXISTS use:
存在使用：
SELECT *
FROM A WHERE EXISTS ( SELECT B.Key FROM B WHERE A.Key = B.Key )
Not Exists In:
不存在于：
SELECT *
FROM A WHERE NOT EXISTS ( SELECT B.Key FROM B WHERE A.Key = B.Key )
Result:
结果：
Key Data
2 somedata2
回答by HLGEM
Left outer join is more flexible than where not exists. You must use a left outer join if you want to return any of the columns from the child table. You can also use the left outer join to return records that match the parent table as well as all records in the parent table that have no match. Where not exists only lets you return the records with no match.
左外连接比不存在的地方更灵活。如果要返回子表中的任何列，则必须使用左外部联接。您还可以使用左外连接返回与父表匹配的记录以及父表中没有匹配的所有记录。如果不存在，则只允许您返回不匹配的记录。
However in the case where they do return the equivalent rows and you do not need any of the columns in the right table, then where exists is likely to be the more performant choice (at least in SQL server, I don't know about other dbs).
但是，如果它们确实返回等效行并且您不需要右表中的任何列，那么 where exists 可能是性能更高的选择（至少在 SQL Server 中，我不知道其他数据库）。
回答by onedaywhen
I suspect the answer ultimately is, both are used (among other constructs) to perform the relational operation antijoinin SQL.
我怀疑最终的答案是，两者都用于（以及其他构造）antijoin在 SQL 中执行关系操作。
回答by Hasski
I suspect the OP wanted to know which construct is better when they are functionallythe same (ie I want to see only rows where there is no match in the secondary table).
我怀疑 OP 想知道当它们在功能上相同时哪个构造更好（即我只想查看辅助表中没有匹配项的行）。
As such, WHERE NOT EXISTS will always be as quick or quicker, so is a good habit to get into.
因此，WHERE NOT EXISTS 总是一样快或快，所以养成一个好习惯。
