如何在MySQL中查找所有外键为空的结果？

Question

我有两张桌子。MySQL中的meat和potatoes。

meat_id是meat表的主键，potatoes表中有一个链接这两个表的meat_id。我要查找potatoes表中没有有效meat_id的所有行。有什么想法吗？

Answer 1

使用LEFT JOIN/为空：

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

使用NOT EXISTS：

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

使用NOT IN：

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

摘要

LEFT JOIN/IS NULL is the best performing option if the column(s) compared in the join can not be NULL。如果这些值为, then NOT EXISTS or NOT IN perform best。

Answer 2

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

Answer 3

执行左连接并使用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