如何找出MySQL查询IN子句中不存在于表中的值

针对你的需求，这里提供几种直接的SQL查询方法，来找出IN子句里不存在于users表的collection_id值：

方法1：使用行构造器+左连接（适用于PostgreSQL、MySQL 8.0+等支持VALUES行构造的数据库） #

通过构造包含所有待查值的临时数据集，和users表左连接后筛选未匹配的记录：

SELECT target.collection_id AS missing_collection_id
FROM (
    VALUES ('xwkoss'), ('cw2333'), ('abcdeef')
) AS target(collection_id)
LEFT JOIN users ON target.collection_id = users.collection_id
WHERE users.collection_id IS NULL;

该查询会返回abcdeef，也就是不在表中的值。

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

同样先构造临时数据集，通过NOT EXISTS检查每个值是否存在于users表：

SELECT target.collection_id AS missing_collection_id
FROM (
    VALUES ('xwkoss'), ('cw2333'), ('abcdeef')
) AS target(collection_id)
WHERE NOT EXISTS (
    SELECT 1 FROM users 
    WHERE users.collection_id = target.collection_id
);

兼容低版本MySQL的写法（无VALUES支持时） #

如果你的MySQL版本低于8.0，不支持VALUES行构造器，可以用UNION ALL来构造临时数据集：

SELECT target.collection_id AS missing_collection_id
FROM (
    SELECT 'xwkoss' AS collection_id
    UNION ALL SELECT 'cw2333'
    UNION ALL SELECT 'abcdeef'
) AS target
LEFT JOIN users ON target.collection_id = users.collection_id
WHERE users.collection_id IS NULL;

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