What's the best way to use LEFT OUTER JOIN to check for non-existence of related rows
SELECT count(id) FROM X LEFT OUTER JOIN Y ON (y.X_id = X.id AND y.foo = 'bar')WHERE y.X_id is nullYou 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 beLEFT OUTER JOIN Y ON-- This however is allowedLEFT OUTER JOIN table2 as Y ON ....
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
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.