"where not in" - bizarre return in a simplest query

Question

I'm hitting the wall how to interpret this:

select * from (
  select '123' id1 from dual
) src
where src.id1 not in (select id1 from table1)
;

Gives me no rows

select * from (
  select '123' id1 from dual
) src
where src.id1 in (select id1 from table1)
;

Also gives me no rows.

How is this possible in both cases?

Where not exists, on the other hand, works as intended. Table1 is not empty. And actually, yes - table1 has both nulls and non-nulls. Why would that make opposite conditions both wrong?

EDIT: Not full duplicate, since the listed similar question was why results are different, whilst here - why results are the same on an opposite condition - which may be confusing.

You should illustrate this question with sample data. Does `select id1 from table1` return (only/some) `NULL`s by any chance? — sticky bit, Jan 23 '19 at 00:57
Guessing from no sample data given: Looks like `table1` doesn't include `'123'` but `NULL` in `id1`. Why the `IN` isn't true is obvious. For the other case see here: https://stackoverflow.com/questions/129077/not-in-clause-and-null-values (though it's about SQL Server, the same applies to Oracle) — sticky bit, Jan 23 '19 at 01:09
Possible duplicate of [NOT IN clause and NULL values](https://stackoverflow.com/questions/129077/not-in-clause-and-null-values) — Thilo, Jan 23 '19 at 01:16

score 1 · Accepted Answer · answered Jan 23 '19 at 01:17

It looks like your list of ids to test against contains NULL values.

Comparisons with NULL always return false. Even WHERE NULL = NULL is false (same as WHERE NULL != NULL). You'd need to use IS NULL.

In your case, you probably want select id1 from table1 where id1 is not null

"where not in" - bizarre return in a simplest query

1 Answers1