CODEWITHSUNDEEP
sqlpostgresql
Jose Cabrera Zuniga
Jose Cabrera Zuniga

Reputation: 2613

difference between use of NOT in SQL

Is there a difference between:

SELECT .... WHERE id NOT IN ( ... some values ... )

and

SELECT .... WHERE NOT id IN ( ... some values ... )

I had problems with the first one if id had null values. In this case the rows with the null values were not considered in the result set but if I did:

SELECT ... WHERE id is NULL

I got a non empty result set. Why is this? Is this answer DB engine dependent?

The queries are like:

select id, name from mytable where id not IN ('1', '2','3','4', '5', '6', '7') limit 10;
 id | name 
--------+---------+--------+--------+---------
(0 rows)


select id, name from mytable where id is null limit 10;
....
(10 rows)

In this case the possible values for the id column are '1', ... , '7', null

Upvotes: 0

Views: 54

Answers (2)

Belayer
Belayer

Reputation: 14861

Yes, Null is difficult to get your head around. Because it doesn't follow boolean logic. One way of looking at it is that it is saying "I do not know" (or in some cases "I do not care'). So to answer your question, "is NULL element of a list like ('1', '2', '3')?" the answer is Null, further if you modify the question to "is NULL element NOT of a list like ('1', '2', '3')?" the answer is still Null. This holds all boolean and most string operators (see fiddle). Safest process, until you get used to it, when in doubt TEST IT, but don't let the result surprise you.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The two are exactly equivalent. Both return nothing if any of the values are NULL.

If any of the values in the NOT IN list are NULL, then NOT IN returns either FALSE (if the value is in the list) or NULL.

On the other hand, if any of the values in the list are NULL, then IN returns either TRUE or NULL. That is fine. Negating that produces either TRUE or NULL.

Upvotes: 1

Related Questions