August 29, 2008 at 4:35 am
I'm getting very confused! It's easily done with me! If anyone can help with what is probably a very basic question, I'd be eternally grateful!
I have a view that only returns one record, I have a table that contains many records including the one in the view. I need to do a join that finds the record that matches the view in the table.
Some of the fields contain nulls, if I join just on the fields that are not null, I get two records back because the values are not unique, if I join on all possible values (some of which are null in this case), I don't get anything back from the table.
Do joins not treat nulls as matching values? I thought that if the corresponding view and table values were the same (a value or a null) it should have returned the record I was looking for but it doeesn't.
Thanks
August 29, 2008 at 5:39 am
Hello,
From BOL:-
The SQL-92 standard requires that an equals (=) or not equal to ( ) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To ( ) comparison operators do not follow the SQL-92 standard. A SELECT statement that uses WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows with nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
Hope that helps - I had to read it twice 😉
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 29, 2008 at 6:00 am
Hello again,
I should also have mentioned that if you don’t want to change your settings, you could use the IsNull function in the Join e.g. On IsNull(Table1.Field1, 1) = IsNull(Table2.Field2, 1)
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 29, 2008 at 6:37 am
Thanks. I used
SELECT*
FROM@t1 AS t1
right JOIN@t2 AS t2 ON t2.i = t1.i or (t2.i is null and t1.i is null)
it worked great.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply