May 17, 2011 at 8:35 am
I've run into a scenario where I need to set ANSI_NULLS to OFF - meaning that I need for NULL = NULL to return true. For this query, I set the ANSI_NULLS behavior as such:
SET ANSI_NULLS OFF
GO
Now I run a test query to confirm the expected behavior:
SELECT CASE WHEN NULL = NULL THEN 'Yes' ELSE 'No' END [Result]
And the result I receive is 'Yes'. So far so good.
So now I role this logic into a 2-table INNER JOIN operation. I'm joining on a column which is NULL in both tables. I get no results from the following query, where I should receive 52 rows:
SELECT m.GenericMappingKey, m.PlacementKey, m.PackageName, u.*
FROM StagingDB.dbo.GenericUpload u
INNER JOIN GenericMapping m
ON u.PackageName = m.PackageName
So the comparison of the two NULL values doesn't work as I expect. I modify the query slightly to simplify, and compare the PackageName value to a literal NULL:
SELECT * FROM GenericUpload
WHERE PackageName = NULL
OK, this works fine - I get the data I expect. Now I'll compare the PackageName value to itself on the same table. No results are returned for this query, though I expect to get the same results as the previous example:
SELECT * FROM GenericUpload
WHERE PackageName = PackageName
So the bottom line is that if I compare a value to a literal NULL it works fine but comparing two fields containing a NULL value yields no match. Is this the expected behavior of the ANSI_NULLS OFF setting?
Thanks in advance....
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
May 17, 2011 at 10:36 am
Update:
Mark Broadbent (http://twitter.com/retracement) pointed out to me that BOL references this in the documentation. Specifically, overriding the ANSI_NULLS setting has no impact on INNER JOIN operations. I was able to confirm this behavior by testing my queries on a second machine. Thanks Mark for pointing this out!
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
May 17, 2011 at 10:46 am
No fair... you're not supposed to answer your own questions.
Nice catch tho, thanks for the feedback. You should post it as a QOTD.
May 17, 2011 at 11:30 am
Ninja's_RGR'us (5/17/2011)
No fair... you're not supposed to answer your own questions.
If I didn't answer my own questions, the voices in my head would get lonely.
Ninja's_RGR'us (5/17/2011)
Nice catch tho, thanks for the feedback. You should post it as a QOTD.
That's not a bad idea. This little bug bit me, and I'd expect it could help prevent hours of hair-pulling for someone else in the future.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
May 17, 2011 at 11:42 am
Tim Mitchell (5/17/2011)
Ninja's_RGR'us (5/17/2011)
No fair... you're not supposed to answer your own questions.If I didn't answer my own questions, the voices in my head would get lonely.
Ninja's_RGR'us (5/17/2011)
Nice catch tho, thanks for the feedback. You should post it as a QOTD.That's not a bad idea. This little bug bit me, and I'd expect it could help prevent hours of hair-pulling for someone else in the future.
Exactly my point :hehe:.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy