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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | 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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | 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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | 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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply