如何查询不在MySQL数据表中的指定值？

现有names数据表，结构及数据如下：

# names
+----+-----------+
| id |   name    |
+----+-----------+
| 1  | Jack      |
| 2  | Peter     |
| 3  | Alex      |
| 4  | Albert    |
| 5  | Martin    |
| 6  | Cristian  |
+----+-----------+

需要从指定值'Ali'、'Alex'中，查询出不存在于names表的name值（期望返回Ali）。已知IN()会返回存在的值（执行SELECT name FROM names WHERE name IN ('Ali', 'Alex');得到Alex），而NOT IN()无法满足需求，求可行解决方案。

可行方案 #

方案1：构造候选列表 + LEFT JOIN #

先通过UNION ALL将待检查的所有值构造成临时数据集，再与names表做左连接，筛选出匹配不到原表记录的候选值：

SELECT candidate.name
FROM (
    SELECT 'Ali' AS name
    UNION ALL
    SELECT 'Alex' AS name
) AS candidate
LEFT JOIN names ON candidate.name = names.name
WHERE names.name IS NULL;

执行结果：

+------+
| name |
+------+
| Ali  |
+------+

方案2：使用NOT EXISTS子查询 #

同样先构造候选列表，通过NOT EXISTS子查询判断候选值是否不存在于names表中：

SELECT candidate.name
FROM (
    SELECT 'Ali' AS name
    UNION ALL
    SELECT 'Alex' AS name
) AS candidate
WHERE NOT EXISTS (
    SELECT 1 FROM names WHERE names.name = candidate.name
);

该语句也会返回目标结果Ali。

为什么NOT IN()无法满足需求？ #

NOT IN()的逻辑是从names表中筛选出不在指定列表里的记录，而我们的需求是从指定列表里筛选出不在names表中的值，二者逻辑方向相反，因此直接使用NOT IN()无法得到期望结果。

内容的提问来源于stack exchange，提问作者Martin AJ