NULL returned from Subselect - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

NULL returned from Subselect

Author

Topic

srouse
Starting Member

7 Posts

Posted - 2010-02-08 : 12:16:24

While scrubbing some data I came across some unexpected behavior from a subselect.
If the resultset from the subselect contains a NULL, zero rows are returned.
I understand NULLs are weird, but I don't understand this.

Any thoughts? Details below.

Scott

i.e. If loginname2 in the subselect contains a NULL, zero rows are returned.
select *
from #test1
where loginname1 not in (select loginname2 from #test2)

Example code:

create table #test1(
tid1 int identity,
loginname1 varchar(20) NULL
)

create table #test2(
tid2 int identity,
loginname2 varchar(20) NULL
)

insert into #test1 values ('Bob')
insert into #test2 values ('Bob')
insert into #test1 values ('Sam')
insert into #test2 values ('Harry')

select *
from #test1
where loginname1 not in (select loginname2 from #test2)

--returns Sam as expected

update #test2
set loginname2 = NULL
where tid2 = 2

select *
from #test1
where loginname1 not in (select loginname2 from #test2)

--returns no rows
select *
from #test1
where loginname1 not in (select loginname2 from #test2 where loginname2 is not NULL)

--returns Sam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 12:31:29

I think it because not in evaluates to true only if all results inside evaluates to false. so in first case the subquery returns ('Bob','Harry') so that for Sam
Sam = Bob false,Sam=Harry=False since all comparison evaluates to false not in returns true

in second case the comparison becomes
Sam= Bob false,Sam=NULL unknown since all comparison does not evaluate to false ( 1 is unknown) it wont return value

in third case the subquery will contain only Bob (as you've used filter where loginname2 is not NULL)
so Sam=Bob is false so here only one condition and thats false so it returns Sam

Please note that NULL by itself is not a value but it represents unknown value so all equality operations will return only unkown with NULL as default.

you could however override this default behaviour by changing ANSI NULLs setting

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-08 : 13:22:19

Visakh's description is accurate. And it's all about truth tables.. I've copied them from BOL at the bottom of this post.

But, to reitterate how IN and NOT IN work. NOT IN acts as a set of AND operations where as IN acts as a set of OR operations.

For NOT IN the important truth evaluations are:
TRUE AND NULL = UNKNOWN
FALSE AND NULL = FALSE

Where as for IN it's:
TRUE OR NULL = TRUE
FASLE OR NULL = UNKNOWN

The following chart shows the outcomes when you compare TRUE and FALSE values by using the AND operator.

 AND    | TRUE    | FALSE | UNKNOWN  
--------------------------------------
TRUE    | TRUE    | FALSE | UNKNOWN           
FALSE   | FALSE   | FALSE | FALSE         
UNKNOWN | UNKNOWN | FALSE | UNKNOWN
              

The following table shows the result of the OR operator.

 OR     | TRUE | FALSE   | UNKNOWN  
--------------------------------------
TRUE    | TRUE | TRUE    | TRUE           
FALSE   | TRUE | FALSE   | UNKNOWN         
UNKNOWN | TRUE | UNKNOWN | UNKNOWN

srouse
Starting Member

7 Posts

Posted - 2010-02-08 : 14:18:35

Thanks,

Excellent answers.

I changed the logic from IN/NOT IN to EXISTS/NOT EXISTS and I get the expected result.

Scott

select *
from #test1
where not exists (select loginname2 from #test2 where #test1.loginname1 = #test2.loginname2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources