How <> works when compared with multiple values?-CodePudding

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?

CodePudding user response：

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

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

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

CodePudding user response：

The name of the subquery you wrote is "Corelated subquery". For each row of MyTable with alias A takes the A.col1 value and subquery compares it to every B.col1 from MyTable with alias B. The exists condition is satisfied if at least 1 B.col1 satisfies this condition: B.Col1 <> A.Col1 and when the subquery finds that B.col1 value that satisfied that condition it exits the exists condition and returns it as true it wont search for further B.col1 values that satisfy that condition (B.Col1 <> A.Col1). Hope I was clear. Ps didn't see NOT exists in front of the subquery, not only turns the true value to false so if the condition (B.Col1 <> A.Col1) is not satisfied it will be true.