oracle 使用相同的表，为什么IN、NOT IN、NOT EXISTS和EXISTS有不同的输出？

6ju8rftf 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(144)

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的帖子，但我不确定为什么操作符会产生不同的结果。我希望NOT EXISTS返回两次'Log 67890'，'Log 11222'和'Log 22211'，但输出为空。我还期望EXISTS返回两次“Log 12345”和“Log 33333”。
我还没有看到一篇文章解释使用同一个表时的操作符（在这个例子中，只有一个表'SAMPLE'）。
在这个例子中，使用IN/NOT IN似乎是可行的。有时候我会犯这样的错误：ORA-00913：太多的价值观”
任何帮助将不胜感激！

oracle

来源：https://stackoverflow.com/questions/77251551/using-the-same-table-why-does-in-not-in-not-exists-and-exists-have-different

2条答案

按热度按时间

cgfeq70w1#

子查询

SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room'

返回四个值。
当你执行IN (...)时，你只与这四个结果相关--你的主要查询是在sample中找到四个值的四行。
当你做EXISTS (...)的时候，你没有关联。您正在sample中查找由该子查询返回的行。这是一个全有或全无的决定，对于外部表中的每一行都有相同的答案。如果有 any 行带有'Red room'，则子查询返回一些内容，exists子句的计算结果为true;否则，它评估为假。
如果你想要和IN一样的结果，你需要在子查询中添加相关性：

SELECT 
    * 
 FROM 
    SAMPLE s1
 WHERE 
     EXISTS (
         SELECT NULL -- can be anything as value isn't referred to elsewhere
         FROM SAMPLE s2
         WHERE ROOM_LOG = 'Red Room'
         AND s2.LOG_NAME = s1.LOG_NAME -- correlation with main query
     )

db<>fiddle
加上NOT也会得到与NOT IN相同的给予结果。

赞(0）回复(0）举报 2023-10-16

pdtvr36n2#

这是因为条件不同：

SELECT *
FROM SAMPLE
--  1. rows with LOG_NAMES having ROOM_LOG = Red Room
WHERE     LOG_NAME IN (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')
    -- Results with rows having LOG_NAMES (Log 12345, Log 33333) 

--  2 rows with LOG_NAME not having ROOM_LOG = Red Room
WHERE     LOG_NAME NOT IN (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')
    -- Results with rows NOT having LOG_NAMES (Log 12345, Log 33333)   --> having (Log 11222, Log 22211, Log 67890) 

--  3    if SAMPLE  have no rows with ROOM_LOG = Red Room 
WHERE     NOT EXISTS (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')
    -- Results with no rows because table does contain ROOM_LOG = 'Red Room' 

--  4   if SAMPLE does have at least a row with ROOM_LOG = Red Room 
WHERE     EXISTS (SELECT LOG_NAME FROM SAMPLE WHERE ROOM_LOG = 'Red Room')
    -- Results with all rows because table does contain ROOM_LOG = 'Red Room'

所有结果都符合要求…

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 使用相同的表，为什么IN、NOT IN、NOT EXISTS和EXISTS有不同的输出？

2条答案

相关问题

热门标签

最新问答