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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
2
Sign in
to vote
ID 802156 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/24/2013 5:50:31 PM
Access Restriction Public

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'.

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)