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

image

 

and you might have expected

image

 

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')

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.