Can I MySQL SELECT rows only where JOIN fails?

Question

I'd like to perform a MySQL query such that:

SELECT * FROM table_A JOIN table_B on table_A.id = table_B.foreign_key

…but I'd like to return rows where there is no match in table_B for table_A. Is this possible? How can I accomplish it?

Answer 1

You want to use a LEFT OUTER JOIN and then a WHERE clause to only allow NULL on the joined table.

SELECT * FROM table_A 
LEFT OUTER JOIN table_B ON table_A.id = table_B.foreign_key
WHERE table_B.foreign_key IS NULL

Answer 2

Try this:

SELECT *
FROM table_A
LEFT JOIN table_B on table_A.id = table_B.foreign_key
WHERE table_B.foreign_key IS NULL