Jay Jay - 4 months ago 11
SQL Question

Best practices for SQL JOIN ON IF Field1 IS NULL then Field2 ELSE Field1

I have 2 decent size tables (200k+ rows) that I'm trying to do a basic left join on. There are 2 different ID fields in each table that can be used for joining

ID1 = better to join on but often NULL, alphanumeric so it's a varchar

ID2 = always has a value, numeric

So I'm currently JOINING like this, but after 10 mins or so I'm canceling it:

SELECT a.*,
b.fieldname
FROM TABLE1 a
LEFT JOIN TABLE2 b
ON COALESCE(a.ID1,CONVERT(Varchar(10),a.ID2)) = COALESCE(b.ID1,CONVERT(Varchar(10),b.ID2))


I basically prefer to JOIN ON ID1, but when ID1 is NULL, then JOIN ON ID2

Is there a more efficient way to do this? Help appreciated

**** UPDATE

Think of if the data looked like this in both tables:

ID2 ID1
300 NULL
301 NULL
309 NULL
311 311-A
311 311-B
314 NULL
316 316-1
316 316-3


So as you can see most of the time ID1 is NULL and that's fine because ID2 is unique, but sometimes there is extensions of ID2, and thats when I want to join on ID1

So what I'd be trying to achieve is something like this in psuedocode:

SELECT a.*,
b.fieldname
FROM TABLE1 a
LEFT JOIN TABLE2 b
ON IF a.ID1 IS NOT NULL THEN
a.ID1 = b.ID1
ELSE
a.ID2 = b.ID2
END

Answer

Try an ON clause of:
ON (a.ID1 IS NOT NULL AND a.ID1=b.ID1) OR (a.ID1 IS NULL AND a.ID2=b.ID2)