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