Home Dashboard Directory Help
Search

Why would a NULL value in a subquery cause the outer query to return no rows? by IJustWant2AskSomething


Status: 

Active


1
2
Sign in
to vote
Type: Bug
ID: 802156
Opened: 9/24/2013 5:50:31 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

If my subquery has a NULL value, my outer query returns nothing - I expect my outer query should still work.

Please see my example in 'Steps to Reproduce'.

Details
Sign in to post a comment.
Posted by Microsoft on 5/8/2014 at 5:00 PM
Rob is right. The behavior follows the ANSI SQL semantics of NULL.
Ultimately, your filter condition boils down to:
NOT 9 = NULL

Whether 9=NULL is indeterminate, therefore we cannot say for sure whether this is true or false. Therefore, we cannot assume that 9=NULL is true, and we cannot assume that NOT 9=NULL is true.

--
Jos de Bruijn - SQL Server PM
Posted by Microsoft on 1/5/2014 at 8:24 PM
Thanks for submitting this feedback. We will look into this issue and update you with our findings. Thanks for your continued support in improving SQL Server.
Posted by RobNicholson, MCSM on 9/25/2013 at 1:08 AM
Basically NOT IN has to return a true or a false, when a comparision against a NULL occurs it returns NULL. This causes no rows to be returned from the inner query. Try using NOT EXISTS to get the behaviour you are looking for. The link below gives more detail of why SQL acts this way: http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

select    * from tb_test_sandwiches ts
where NOT EXISTS (select bread_id from tb_test_breadType tb WHERE ts.bread_id = tb.bread_id)
Sign in to post a workaround.