How <> works when compared with multiple values?

Question

I have a table and sample data as below.

create table MyTable
(
Col1 NUMBER,
Col2 VARCHAR2(30)
)

MyTable
Col1    Col2

1    |  Val1
2    |  Val2
3    |  Val3
4    |  Val4

Below is the query which is already written and deployed to the application by some one else.

SELECT Col2
FROM MyTable A WHERE Col1 IN (2,3,4)
AND NOT EXISTS
(SELECT 1
FROM MyTable B
WHERE B.Col1 <> A.Col1) 

How does <> compare multiple values in this case? Does it just compare with value 2? Or randomly compares with any value amoung 2,3 or 4?

Answer 1

The values are compare one by one.

If you have the sample data:

CREATE TABLE MyTable(col1, col2) AS
SELECT 1, 'Val1' FROM DUAL UNION ALL
SELECT 2, 'Val2' FROM DUAL UNION ALL
SELECT 3, 'Val3' FROM DUAL UNION ALL
SELECT 4, 'Val4' FROM DUAL;

Then:

SELECT *
FROM   MyTable A
WHERE  Col1 IN (2,3,4)

Will return 3 rows:

COL1	COL2
2	Val2
3	Val3
4	Val4

For your full query:

SELECT Col2
FROM   MyTable A
WHERE  Col1 IN (2,3,4)
AND    NOT EXISTS(
         SELECT 1
         FROM MyTable B
         WHERE B.Col1 <> A.Col1
       )

Then for each of the rows it will check that a row does NOT EXISTS in the MyTable table where B.Col1 <> A.Col1. In your case, there are 3 rows that exist in the sub-query for each of the matched rows in the main query. You can see this with the query:

SELECT Col2,
       (SELECT LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1)
        FROM   MyTable B
        WHERE  B.Col1 = A.Col1) AS equal,
       (SELECT LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1)
        FROM   MyTable B
        WHERE  B.Col1 <> A.Col1) AS not_equal
FROM   MyTable A
WHERE  Col1 IN (2,3,4)

Which outputs:

COL2	EQUAL	NOT_EQUAL
Val2	2	1,3,4
Val3	3	1,2,4
Val4	4	1,2,3

Given that there is always (more than) one row that exists then the NOT EXISTS condition will exclude every row and your result set will be empty.

db<>fiddle here