Null and Empty

Question

Null and Empty

JuanBob

SSCertifiable

Points: 5059
More actions
October 27, 2005 at 9:53 am

#91861

I am joining two tables based on two fields such as:
a.CustomerID = b.CustomerID and a.LocationID = b.LocationID
The problem is that if a.LocationID is Null, it will not match if b.LocationID = ' '. Is there any way around this?
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

and isnull(a.LocationID) = isnull(b.LocationID)

Maybe this is one case where I would try to avoid the nulls by putting a default value, as this solution will slow down the query (by how much is depending on the indexes).

JuanBob SSCertifiable Points: 5059 More actions · Answer 2

Thank you, that makes sense! I got it to work using

isnull(b.LocationID, ' ') = isnull(b.LocationID, ' ')

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

You can also try:

a.CustomerID = b.CustomerID and a.LocationID = NULLIF(b.LocationID,'')

Andy

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Were you aware that if a SQL statement uses functions, indexes may not be used ? This can have an adverse performance effect.

Could an outer join be the solution ?

select ....

from a

LEFT OUTER JOIN b

on a.CustomerID = b.CustomerID

and a.LocationID = b.LocationID

SQL = Scarcely Qualifies as a Language

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

That's the point I was making with my performance hint, however I don't see how '' with equal null with the left join option???

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

So you want the condition of null = '' to be true?

I strongly recommend that you fix the data and not try to do this with ISNULL = ISNULL

See below for a test case:

drop table Customers

drop table CustomerLocations

go

Create table Customers

(CustomerID varchar(12)not null

,LocationIDvarchar(12) NULL -- Primary location

, CDvarchar(255)

, primary key (CustomerID)

)

Create table CustomerLocations

(CustomerID varchar(12)not null

,LocationIDvarchar(12) NULL -- Primary location

, LDvarchar(255)

-- , primary key (??)

)

Insert into Customers

(CustomerID , LocationID , CD )

select 'C-A' , 'L-A' , 'Customers.LocationId has a value ' union all

select 'C-B' , null , 'Customers.LocationId is null ' union all

select 'C-C' , '' , 'Customers.LocationId is zero length characters ' union all

select 'C-D' , '' , 'Customers.LocationId is zero length characters '

Insert into CustomerLocations

(CustomerID , LocationID , LD )

select 'C-A' , 'L-A' , 'CustomerLocations.LocationId has a value ' union all

select 'C-B' , '' , 'CustomerLocations.LocationId is zero length characters ' union all

select 'C-C' , '' , 'CustomerLocations.LocationId is zero length characters ' union all

select 'C-D' , null , 'Customers.LocationId is null '

go

select *

from Customers

LEFT OUTER JOIN CustomerLocations

on CustomerLocations.CustomerId = Customers.CustomerId

and CustomerLocations.LocationID= Customers.LocationID

select *

from Customers

LEFT OUTER JOIN CustomerLocations

on CustomerLocations.CustomerId = Customers.CustomerId

and COALESCE( CustomerLocations.LocationID, '') = COALESCE( Customers.LocationID, '' )

SQL = Scarcely Qualifies as a Language