What's the best way to use LEFT OUTER JOIN to

问题:

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.

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....?

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.

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.

Edits: corrected transcription error (ON not AS) which was pointed out by several responses. Fixed grammatical error.

回答1:

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 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 ....

回答2:

Checking if the primary key of table Y is NULL would do the trick, which tells the join did not matched :

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

回答3:

Johan's answer is correct 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.

回答4:

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'

回答5:

You have to remember that NULL is special value! And that's why i mysql doc's You have a chapter called "4.3.4.6 Working with NULL Values".

link: https://dev.mysql.com/doc/refman/5.7/en/working-with-null.html

enjoy!