PL/SQL (MOSC)

MOSC Banner

two tricky SQLs with NULL in IN list

edited Nov 10, 2010 3:37PM in PL/SQL (MOSC) 11 commentsAnswered ✓
Hi All,

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 

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center