Null vs Not null

Database
Enthusiast

Null vs Not null

Sel a,b from table A where (a,b) not in(sel x,y from table B)
Table b is having col x that can be null.
bteq involving this query is taking 10 hrs to run.
but when i am changing col x of table B to be a not null column same bteq is taking less than an hour.
Can anyone please clarify me what affect changing a col from null to not null has done here.
Please help me out in this as we can use same concept in many of our BTEQs....
3 REPLIES
Enthusiast

Re: Null vs Not null

can anybody respond anything ?
are there no takers in this forum?
tell me if the question is confusing or whatever but please respond guys.................
Junior Contributor

Re: Null vs Not null

There's a basic rule for all DBMSes:
Never use NOT IN on NULLable columns, because this might return unexpected result sets, because it will use three-way logic.
Compare both Explains and you'll see the difference.

Better use NOT EXISTS:
Sel a,b from table A where not exists(sel * from from table B where a.a = b.x and a.b = b.y)

Dieter
Enthusiast

Re: Null vs Not null

Actually i figured it......
But thankx for your reply.....