SQL 查询， NOT IN 和 NOT EXISTS 使用区别

有两张表

application 和 sys_archive ,  通过 application_id 进行关联。

application	sys_archive
application_id	sys_archive_id
workflowStatus	application_id
	status

场景： application 执行备份后，会往 sys_archive 插入一条记录，成功status 为1，失败为-1。

需求：想要查询出，没有备份过和备份过但是没有成功的。（假设需要备份的application 条件为 workflowStatus == 1）

使用以下SQL 查询时，当sys_archive 中有对应的记录时，结果不为空，当清空sys_archive 后，结果总是为空

SELECT
	app.* 
FROM
	application app 
WHERE
	app.workflowStatus = 1 
	AND 
    app.application_id NOT IN 
        (SELECT application_id FROM sys_archive WHERE STATUS = 1 );

ChatGpt 分析如下：

你的查询逻辑上是正确的，但可能导致结果为空的原因包括：

• 子查询返回值：确保子查询 SELECT arc.application_id FROM sys_archive arc WHERE STATUS = 1 返回的 application_id 列表中确实有值。如果这个子查询返回的结果为空，NOT IN 将会导致主查询也返回空结果。

• NULL 值的影响：如果 sys_archive 表中的 application_id 列有 NULL 值，这也会导致主查询返回空结果。因为在 SQL 中，NOT IN 会在遇到 NULL 时无法确定，结果会被视为未知，从而导致整个条件返回 FALSE。

 为了避免这类问题，可以改用 NOT EXISTS，它处理 NULL 值更为健壮：

SELECT
    app.* 
FROM
    application app 
WHERE
    app.workflow = 1 
    AND NOT EXISTS (
        SELECT 1 
        FROM sys_archive arc 
        WHERE arc.application_id = app.application_id 
        AND arc.STATUS = 1
    );

这个查询逻辑与原始查询相同，但使用 NOT EXISTS，它在判断是否存在符合条件的记录时不会受到 NULL 值的影响。这样应该可以得到你想要的结果

PS:

在 SQL 中，使用 = 比较 NULL 值时，结果将永远为 FALSE。所以，sa1_0.is_internal = NULL 和 NULL = NULL 这两种比较都不会返回预期的结果。