使用 LEFT OUTER JOIN 检查相关行不存在的最佳方法是什么
使用 MySQL 5.x 我想有效地从表 X 中选择表 Y 中没有相关行满足某些条件的所有行，例如
Using MySQL 5.x I want to efficiently select all rows from table X where there is no related row in table Y satisfying some condition, e.g.
给我 X 中所有与 foo = bar 相关的 Y 不存在的记录
Give me all records in X where a related Y with foo = bar does NOT exist
SELECT count(id) FROM X
LEFT OUTER JOIN Y ON y.X_id = X.id AND y.foo = 'bar'
WHERE y....?
据我所知，左外连接保证为左(第一个)表中的每一行生成一行——在本例中为 X——无论是否在连接表中找到了令人满意的行.我想要做的就是只选择那些没有找到行的行.
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.
在我看来，如果没有匹配的记录，y.X_id 应该是 NULL，但是这个测试似乎不起作用.y.X_id = 0 或 !y.X_id 也不是.
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.
编辑:更正了多个回复指出的转录错误(ON 不是 AS).修正语法错误.
Edits: corrected transcription error (ON not AS) which was pointed out by several responses. Fixed grammatical error.
推荐答案
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
你已经接近了.
首先照常进行连接，然后选择 Y 中 not null 行实际上是 null 的所有行，因此您确定存在不匹配"" 而不仅仅是 Y 中的 null 值.
First do the join as normal, then select all rows for which a not null row in Y is in fact null, so you are sure there's a "no match" and not just a null value in Y.
还要注意您在查询中的拼写错误(已更正):
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 ....
相关文章