mysql - 从一个表返回另一个表中不存在的值

Question

我有两张表，一张:

INSERT INTO `fictional_companies` (`ID`, `name`)
VALUES
    (8209948, 'Moulon Rouge LTD'),
    (11399177, 'Burgers Burgers Burgers LTD');

还有两个:

INSERT INTO `processed` (`ID`)
VALUES
    ('8209948');

我想从 fictional_companies 表中返回结果，但前提是它们不存在于 processed 表中。

在示例情况下，这将是结果:

(11399177, 'Burgers Burgers Burgers LTD')

Answer 1

LEFT JOIN 根据与 IS NULL 的正确关系检查右侧表，将获取与右侧表不匹配的那些行

SELECT fc.*
FROM fictional_companies AS fc
LEFT JOIN processed AS p ON p.ID = fc.ID
WHERE p.ID IS NULL

---

另一种方法可以使用 Correlated Subqueries with Not Exists

SELECT fc.*
FROM fictional_companies AS fc
WHERE NOT EXISTS (SELECT 1 FROM processed AS p 
                  WHERE p.ID = fc.ID)