同一表中IN/NOT IN与EXISTS/NOT EXISTS输出差异及使用困惑

测试数据与SQL执行结果 #

测试表定义与IN查询（匹配Red Room关联的LOG_NAME） #

WITH SAMPLE (DATE_OF_USE, VENUE, ROOM_LOG, LOG_NAME, PERSON_LOG) AS
(
    SELECT DATE '2023-09-01', 'ASU', 'Red Room', 'Log 12345', 'Jane, Doe' 
    FROM DUAL 
    UNION ALL
    SELECT DATE '2023-09-01', 'ASU', 'ROOM 01', 'Log 12345', 'Jane, Doe' 
    FROM DUAL 
    UNION ALL
    SELECT DATE '2023-09-02', 'MOR', 'Blue Room', 'Log 67890', 'John, Smith'  
    FROM DUAL 
    UNION ALL
    SELECT DATE '2023-09-02', 'MOR', 'ROOM 02', 'Log 67890', 'John, Smith' 
    FROM DUAL 
    UNION ALL
    SELECT DATE '2023-09-03', 'ASU', 'ROOM 03', 'Log 11222', 'Luis, Jacob' 
    FROM DUAL 
    UNION ALL
    SELECT DATE '2023-09-04', 'MOR', 'ROOM 04', 'Log 22211', 'Patel, Hannah' 
    FROM DUAL 
    UNION ALL
    SELECT DATE '2023-09-05', 'ASU', 'Red Room', 'Log 33333', 'Sparks, Noah' 
    FROM DUAL 
    UNION ALL
    SELECT DATE '2023-09-05', 'ASU', 'ROOM 01', 'Log 33333', 'Sparks, Noah' 
    FROM DUAL
)
SELECT 
    * 
FROM 
    SAMPLE
WHERE 
    LOG_NAME IN (SELECT LOG_NAME FROM SAMPLE 
                 WHERE ROOM_LOG = 'Red Room')

执行结果：

2023-09-05 00:00:00 ASU ROOM 01 Log 33333 Sparks, Noah
2023-09-05 00:00:00 ASU Red Room Log 33333 Sparks, Noah
2023-09-01 00:00:00 ASU ROOM 01 Log 12345 Jane, Doe
2023-09-01 00:00:00 ASU Red Room Log 12345 Jane, Doe

NOT IN查询（排除Red Room关联的LOG_NAME） #

SELECT 
    * 
 FROM 
    SAMPLE
 WHERE 
     LOG_NAME NOT IN (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')

执行结果：

2023-09-03 00:00:00 ASU ROOM 03 Log 11222 Luis, Jacob
2023-09-02 00:00:00 MOR ROOM 02 Log 67890 John, Smith
2023-09-02 00:00:00 MOR Blue Room Log 67890 John, Smith
2023-09-04 00:00:00 MOR ROOM 04 Log 22211 Patel, Hannah

无关联NOT EXISTS查询 #

SELECT 
    * 
 FROM 
    SAMPLE
 WHERE 
     NOT EXISTS (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')

执行结果：空集

无关联EXISTS查询 #

SELECT 
    * 
 FROM 
    SAMPLE
 WHERE 
     EXISTS (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')

执行结果：全表数据

疑问解答 #

1. EXISTS/NOT EXISTS结果不符合预期的原因 #

你写的EXISTS/NOT EXISTS子查询没有关联主表，导致子查询的判断逻辑和主表每行数据完全无关：

• EXISTS (SELECT ... WHERE ROOM_LOG='Red Room')：只要整个SAMPLE表中存在任意一条ROOM_LOG为Red Room的记录，这个条件就为真，因此返回全表所有行。
• NOT EXISTS (...)：因为表中确实存在Red Room的记录，所以条件为假，返回空集。

要实现和IN/NOT IN相同的逻辑，必须在子查询中关联主表的LOG_NAME字段，正确写法如下：

-- 等价于LOG_NAME IN (...)
SELECT * 
FROM SAMPLE s1
WHERE EXISTS (
    SELECT 1 
    FROM SAMPLE s2 
    WHERE s2.ROOM_LOG = 'Red Room' 
    AND s2.LOG_NAME = s1.LOG_NAME
)

-- 等价于LOG_NAME NOT IN (...)
SELECT * 
FROM SAMPLE s1
WHERE NOT EXISTS (
    SELECT 1 
    FROM SAMPLE s2 
    WHERE s2.ROOM_LOG = 'Red Room' 
    AND s2.LOG_NAME = s1.LOG_NAME
)

这两个查询会返回和你预期一致的结果。

2. 单表场景下IN/NOT IN与EXISTS/NOT EXISTS的核心差异 #

• IN/NOT IN：子查询返回一个值列表，主表每行的指定字段和这个列表做匹配，本质是值集合的比对。
• EXISTS/NOT EXISTS：默认是判断子查询是否有结果返回，但如果要和IN逻辑对齐，必须关联主表字段，此时是逐行判断主表当前行是否满足子查询的关联条件，本质是行级存在性判断。

单表场景下，二者可以实现相同逻辑，但EXISTS在处理大数据量时通常性能更优，因为它找到匹配项就会停止子查询的遍历，而IN需要先构建完整的值列表。

3. ORA-00913: too many values错误原因及解决 #

这个错误的核心是IN子句左右两边的字段/值数量不匹配：

• 情况1：多字段匹配时，比如WHERE (col1, col2) IN (SELECT col3 FROM t2)，子查询返回1列，但左边是2个字段，数量不匹配。
• 情况2：手动写值列表时出错，比如WHERE (col1, col2) IN (1,2)（左边2字段，右边单值），或者值列表末尾多写了逗号。

解决方法：

• 多字段匹配时，确保子查询返回的列数和左边字段数完全一致，比如WHERE (col1, col2) IN (SELECT col3, col4 FROM t2)。
• 检查手动编写的值列表，确保格式正确，没有多余逗号或数量不匹配的情况。

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