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
Advertisement
Answer
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.