CODEWITHSUNDEEP
sqloracle-database
Harshana
Harshana

Reputation: 7647

Difference of NOT IN and NOT EQUALS different behaviour in SQL query

I thought NOT IN behaves the same as != in a query. But a query using != returns more rows than a query using NOT IN: 

SELECT  count(A.NO)
FROM A
WHERE
A.CODE != 'a' 
AND 
A.CODE  != 'b'
AND
A.CODE  != 'c'
AND 
A.NAME  != 'd' 
AND
A.NAME  != 'e'

returns 1566 rows, whereas

SELECT  count(A.NO)
FROM A
WHERE
A.CODE NOT IN ('a','b','c')
AND
A.NAME NOT IN ('d','e')

returns only 1200 rows.

I suppose NOT IN excludes NULL values - would that be the only difference?

Upvotes: 6

Views: 156

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109613

No answer but what delivers the following?

SELECT *
FROM A
WHERE
A.CODE != 'a' 
AND 
A.CODE  != 'b'
AND
A.CODE  != 'c'
AND 
A.NAME  != 'd' 
AND
A.NAME  != 'e'
AND NOT (
    A.CODE NOT IN ('a','b','c')
    AND
    A.NAME NOT IN ('d','e') 
)
LIMIT 0, 20

Upvotes: 0

Dale M
Dale M

Reputation: 2473

I have tried to replecate the problem using this simplified SQL fiddle, however, it returns the same number for both versions.

What is differant about your data?

Upvotes: 2

Related Questions