two tricky SQLs with NULL in IN list
select 'true' from dual where (1,2) not in ((2,3),(2,null)); --One row
select 'true' from dual where (1,null) not in ((1,2),(2,3)); -- 0 row
Could someone tell me why it is, please?
As far as I know, the first SQL will be translated to
(1,2) !=(2,3) AND (1,2) != (2, null)
Since NULL is invovled, the latter branch will be evaluated to NULL(I guess), then no row should be returned.
I am completely confused by the difference between these two SQLs.
Best regards,
Leon