How can I find all results with a null foreign key in MySQL?

Question

I have 2 tables... meat and potatoes in MySQL.

meat_id is the primary key for the meat table and there is a meat_id in the potatoes table that links the 2 tables. I want to find all rows in the potatoes table that don't have a valid meat_id. Any ideas?

Answer 1

 SELECT *
 FROM potatoes p
 WHERE NOT EXISTS (SELECT 1 from meat m where m.meat_id = p.meat_id)

Answer 2

Using LEFT JOIN/IS NULL:

   SELECT p.*
     FROM POTATOES p
LEFT JOIN MEAT m ON m.meat_id = p.meat_id
    WHERE m.meat_id IS NULL

Using NOT EXISTS:

SELECT p.*
  FROM POTATOES p
 WHERE NOT EXISTS(SELECT NULL
                    FROM MEAT m
                   WHERE m.meat_id = p.meat_id)

Using NOT IN:

SELECT p.*
  FROM POTATOES p
 WHERE p.meat_id NOT IN (SELECT m.meat_id
                           FROM MEAT m)

Summary

LEFT JOIN/IS NULL is the best performing option if the column(s) compared in the join can not be NULL. If those values can be NULL, then NOT EXISTS or NOT IN perform best.

Answer 3

Do a left join and use having:

SELECT p.potato_id, m.meat_id
FROM potatos p
LEFT JOIN meat m ON p.meat_id = m.meat_id
HAVING m.meat_id IS null