使用LEFT OUTER JOIN检查相关行是否不存在的最佳方法是什么 [英] What's the best way to use LEFT OUTER JOIN to check for non-existence of related rows

问题描述

使用MySQL 5.x，我想高效从表X中选择所有满足条件的表X中没有没有相关行的行，例如

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.

编辑:已纠正的转录错误(不是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 ....

这篇关于使用LEFT OUTER JOIN检查相关行是否不存在的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！