May 2, 2005 at 7:20 pm
Iam trying to select data from two tables looks like this
TABLE1
COL1 COL2 COL3 COL4 COL5 COL6
1 2 1 XX X NULL
2 2 1 XX X XX
3 3 4 XX X XXX
4 4 2 X NULL XXX
TABLE2
COL1 COL2 COL3
1 2 1
2 2 1
3 3 4
4 4 2
here is my query
SELECT TABLE1.* FROM TABLE1, TABLE2
WHERE TABLE1.COL1 = TABLE2.COL1
AND TABLE1.COL2 = TABLE2.COL2
AND TABLE1.COL3 = TABLE2.COL3
Iam trying to run this in a stored procedure with "SET ANSI_NULLS OFF", when i run this
result is taking out records with null data in it.
COL1 COL2 COL3 COL4 COL5 COL6
2 2 1 XX X XX
3 3 4 XX X XXX
When I tried to run this with ANSI_NULLS ON then its giving me correct result.
I think with ansi nulls off its going to eliminate null records only if exist in columns used in where condition to map data.
Did anyone had this problem before?
Why is it showing results like this??
Thanks
ash
May 3, 2005 at 5:21 am
As you might have guessed, it is a question of null handling, and one thing that sets how nulls are handled are the ANSI_NULL setting.
What you're seeing is how it's supposed to work.
To gain understanding about the how's and why's it is this way, please look in BOL at the paragraph 'NULL Comparison Search Conditions', which has some good info about this behaviour. There are also several other places that explains how to handle nulls in Transact SQL.
/Kenneth
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply