MySQL 使用 LEFT OUTER JOIN 检查相关行不存在的最佳方法是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5886095/
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
What's the best way to use LEFT OUTER JOIN to check for non-existence of related rows
提问by podperson
Using MySQL 5.x I want to efficientlyselect all rows from table X where there is no related rowin table Y satisfying some condition, e.g.
使用 MySQL 5.x 我想有效地从表 X 中选择所有行，其中表 Y 中没有满足某些条件的相关行，例如
Give me all records in X where a related Y with foo = bar does NOT exist
给我 X 中所有与 foo = bar 相关的 Y 不存在的记录
SELECT count(id) FROM X
LEFT OUTER JOIN Y ON y.X_id = X.id AND y.foo = 'bar'
WHERE y....?
As I understand it, a left outer join is guaranteed to produce a row for each row in the left (first) table -- X in this case -- whether or not a satisfying row in the joined table was found. What I want to do is then select only those rows where no row was found.
据我了解，左外连接保证为左（第一个）表中的每一行生成一行——在本例中为 X——无论是否在连接表中找到了令人满意的行。我想要做的就是只选择那些没有找到行的行。
It seems to me that y.X_id should be NULL if there is no matching record, but this test doesn't seem to work. Nor does y.X_id = 0 or !y.X_id.
在我看来，如果没有匹配的记录，y.X_id 应该是 NULL，但是这个测试似乎不起作用。y.X_id = 0 或 !y.X_id 也不行。
Edits: corrected transcription error (ON not AS) which was pointed out by several responses. Fixed grammatical error.
编辑：更正了几个回复指出的转录错误（ON 不是 AS）。修正了语法错误。
回答by Johan
SELECT count(id) FROM X
LEFT OUTER JOIN Y ON (y.X_id = X.id AND y.foo = 'bar')
WHERE y.X_id is null
You were close.
你很接近。
First do the join as normal, then select all rows for which a not nullrow in Y is in fact null, so you are sure there's a "no match" and not just a nullvalue in Y.
首先照常进行连接，然后选择not nullY中的一行实际上是的所有行null，因此您确定存在“不匹配”而不仅仅是nullY 中的值。
Also note the typo (since corrected) you made in the query:
还要注意您在查询中所做的错字（已更正）：
LEFT OUTER JOIN Y AS
-- should be
LEFT OUTER JOIN Y ON
-- This however is allowed
LEFT OUTER JOIN table2 as Y ON ....
回答by MaxiWheat
Checking if the primary key of table Y is NULL would do the trick, which tells the join did not matched :
检查表 Y 的主键是否为 NULL 可以解决问题，它告诉连接不匹配：
SELECT count(id) FROM X
LEFT OUTER JOIN Y ON (y.X_id = X.id AND y.foo = 'bar')
WHERE y.Y_id is null
回答by ypercube??
Johan's answer is correct 100%.
约翰的回答是 100% 正确的。
Besides that, there is also this option:
除此之外，还有这个选项：
SELECT count(id)
FROM X
WHERE NOT EXISTS
( SELECT *
FROM Y
WHERE (y.X_id = X.id AND y.foo = 'bar')
)
Depending on your table size and data distribution, this may be more efficient. Test and keep both ways for future reference.
根据您的表大小和数据分布，这可能更有效。测试并保留两种方式以备将来参考。
回答by Ja Loc
You have to remember that NULLis special value! And that's why i mysql doc's You have a chapter called "4.3.4.6 Working with NULL Values".
您必须记住，NULL是特殊值！这就是为什么我在 mysql 文档中有一章名为“4.3.4.6 使用 NULL 值”。
link:https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html
链接：https : //dev.mysql.com/doc/refman/5.7/en/working-with-null.html
enjoy!
请享用！
回答by StevieG
Why use an outer join? Couldn't you just do:
为什么要使用外连接？你不能只做：
SELECT count(id)
FROM X JOIN Y AS y.X_id = X.id AND y.foo <> 'bar'
