Null Values Inside Not in Clause

NULL values inside NOT IN clause

Query A is the same as:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Since 3 = 3 is true, you get a result.

Query B is the same as:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

The NOT IN with NULL values dilemma in ORACLE SQL

This is not an issue with Oracle. This is how SQL is defined.

When the subquery returns a NULL value with NOT IN, then no rows match at all. For this reason, I strongly recommend always using NOT EXISTS instead:

WHEN NOT EXISTS (SELECT 1 FROM bst WHERE x.n = bst.p)
     THEN 'Leaf'

As a corollary, I usually use EXISTS instead of IN, even though it does not have this problem.

Why does this occur? NULL means that the value is unknown, not that the value is "missing" or something else.

So, if all the elements have values, this is easy to calculate:

1 NOT IN (1, 2)  --> false
3 NOT IN (1, 2)  --> true

However:

1 NOT IN (1, 2, NULL)  --> false 
3 NOT IN (1, 2, NULL)  --> NULL, because NULL could be equal to "3"

Basically, if any value is NULL, then NOT IN returns either "false" or NULL. Both "false" and NULL are treated the same in WHEN and WHERE.

using the NOT IN operator on a column with NULL values

Try this way:

select * 
from employee E
where (E.company NOT IN ('ABC', 'DEF')
       or E.company is null)

Understanding SQL IN clause with NULL values

Don't use not in with subqueries. Period. Use not exists; it does what you want:

select x.*
from x
where not exists (select 1 from y where y.y_name = x.x_name);

The problem is this. When you have:

x_name in ('a', 'b', null)

SQL actually returns NULL, not false. However, NULL is treated the same as false in where clauses (and when clauses but not for check constraints). So, the row gets filtered out.

When you negate this, either as:

not x_name in ('a', 'b', null)
x_name not in ('a', 'b', null)

The results is not NULL which is also NULL and everything gets filtered out.

Alas. The simplest solution in my opinion is to get in the habit of using not exists.

IN Clause with NULL or IS NULL

An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work.

(Comment by Marc B)

I would do this for clairity

SELECT *
FROM tbl_name
WHERE 
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)

Null with NOT in sub-query

Using not exists is recommended approach rather than using not in

select * from #FirstTable f
where not exists(select 1 from #secondTable
                 where i = f.i)

How to ignore NULL in WHERE clause when SELECT is used?

To address your specific issue of dealing with null values, you can simply use isnull

SELECT *
  FROM MyTable
 WHERE PartNum   = (SELECT isnull(Value, PartNum) FROM #Variables WHERE VarName = 'PartNum')
   AND PartColor = (SELECT isnull(Value, PartColor) FROM #Variables WHERE VarName = 'PartColor')

Why does WHERE clause removes null values from the result?

Neither "not equal" nor "equal" will select a value that is NULL.

SQL uses "three-way logic" where an expression can be true or false or unknown. NULL is the absence of any value at all so it cannot be equal to something, and if it cannot be equal to a compared value it also cannot be "not equal", instead it is unknown.

To overcome this you need to treat NULL explicitly in your where clause, to include NULLs use OR SIS IS NULL

SELECT 
SIS, COUNT(*)
FROM DL_SQ_DEV_INT.SMRY_DAILY_TRAILER_REPORT
WHERE UPPER(TRIM(SIS)) <> 'EQ' OR SIS IS NULL
GROUP BY 1;

---

Related Topics