9 January, 2014
0 Comments
1 category
If you have a nullable column "Description" in one table and a nullable column "Description" in an other table and you join both tables on this column, records with both null in the field "Description" will not match.
if object_id('[dbo].[Product1]') is null begin create table [dbo].[Product1] ( Id int not null identity(1,1) constraint PK_Product1_Id primary key, [Description] varchar(max) null ) end go if object_id('[dbo].[Product2]') is null begin create table [dbo].[Product2] ( Id int not null identity(1,1) constraint PK_Product2_Id primary key, [Description] varchar(max) null ) end go insert into Product1 values (null) insert into Product1 values ('') insert into Product1 values ('gevuld 1') insert into Product2 values (null) insert into Product2 values ('') insert into Product2 values ('gevuld 1') select p1.*, p2.* from Product1 p1 inner join Product2 p2 on p1.[Description] = p2.[Description]
Results in
and you might have expected
Solution is to use isnull function, like:
select p1.*, p2.* from Product1 p1 inner join Product2 p2 on isnull(p1.[Description], 'Not a value') = isnull(p2.[Description], 'Not a value')
Tags: T-SQL
Category: Uncategorized