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

I have开发者_JAVA技巧 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?

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.

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

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