Not In operator eliminates NULL values rows in a table

Question

I would like to retrieve all rows with values except 1,2,3,4,5 in my COLUMNA in TABLEA .

SELECT * FROM TABLEA WHERE COLUMNA NOT IN (1,2,3,4,5)

But this eliminates the rows with NULL values in COLUMNA too.

I don't want to eliminate NULL values rows and would like to include those rows in the resultset.

Alternatively, I can try below query for the same

SELECT * FROM TABLEA WHERE COLUMNA NOT IN (1,2,3,4,5) OR COLUMNA  IS NULL.

But I would like to know, why is it necessary to add this OR condition?

You can read about sql three valued logic at modern-sql.com/concept/three-valued-logic — Serg
– Serg, Commented May 31, 2019 at 11:56

Gordon Linoff · Accepted Answer · 2019-05-31 11:46:25Z

Why is the additional necessary?

NULL comparisons almost always results in NULL, rather than true or false. A WHERE clause filters out all non-true values, so they get filtered out.

This is how SQL defines NULL. Why is NULL defined like this?

NULL does not mean "missing" in SQL. It means "unknown". So, the result of <unknown> not in (1, 2, 3, 4, 5) is "unknown", because the value could be 1 or it might be 0. Hence it gets filtered.

I will also note that the SQL standard includes NULL-safe comparisons, IS NOT DISTINCT FROM and IS DISTINCT FROM corresponding to = and <> respectively. These treat NULL as just "any other value", so two NULL values are considered equal. However, there is no construct for NULL-safe IN and NOT IN, as far as I know.

Miguel Carreira · Accepted Answer · 2019-05-31 11:50:39Z

3

Try the following:

SELECT * FROM TABLEA WHERE ISNULL(COLUMNA,0) NOT IN (1,2,3,4,5)

answered May 31, 2019 at 11:50

Miguel Carreira

5262 silver badges6 bronze badges

Collectives™ on Stack Overflow

Not In operator eliminates NULL values rows in a table

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related