Sql 使用同一个表，为什么IN、NOT IN、NOT EXISTS和EXISTS有不同的输出

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


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

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

输出:

Blank

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

输出:

2023-09-01 00:00:00 ASU Red Room Log 12345 Jane, Doe
2023-09-01 00:00:00 ASU ROOM 01 Log 12345 Jane, Doe
2023-09-02 00:00:00 MOR Blue Room Log 67890 John, Smith
2023-09-02 00:00:00 MOR ROOM 02 Log 67890 John, Smith
2023-09-03 00:00:00 ASU ROOM 03 Log 11222 Luis, Jacob
2023-09-04 00:00:00 MOR ROOM 04 Log 22211 Patel, Hannah
2023-09-05 00:00:00 ASU Red Room Log 33333 Sparks, Noah
2023-09-05 00:00:00 ASU ROOM 01 Log 33333 Sparks, Noah

使用NOT IN返回我需要的结果，但我看到推荐使用EXISTS/NOT EXISTS而不是使用IN/NOT IN的帖子，但我不确定为什么运算符会产生不同的结果.我原以为不存在两次返回‘Log 67890’、‘Log 11222’和‘Log 22211’，但输出为空.我还期望eXist两次返回‘Log 12345’和‘Log 33333’.

我还没有看到一个帖子解释使用同一个表时的运算符(在这个例子中，只有一个表‘SAMPLE’).

在本例中，使用IN/NOT IN似乎很有效.但是，有时我会遇到这样的错误:‘ORA-00913:值太多.’

如有任何帮助，将不胜感激！