CODEWITHSUNDEEP
sqlsql-server
Abhi
Abhi

Reputation: 151

SQL Join with 2 columns when one of them is NULL/Blank

Let's say I have 2 tables like this: enter image description here

I want to join both #tmp1 & #tmp2 for Columns : Col2 & Col3 . But if the value of either of these columns is blank or Null, I want to ignore that column and just look at one. So for example here, when I join where Col2 is ABC, I should get both DEF & Blank for Col3.

I hope I'm making some sense here. Apologize if it's not clear enough.

Upvotes: 0

Views: 1902

Answers (2)

sgeddes
sgeddes

Reputation: 62861

You can use union for this checking if at least 1 of the 2 columns is the same using exists:

select col1, col2, col3
from tmp1 t1
where exists (select 1
              from tmp2 t2
              where t1.col2 = t2.col2 or t1.col3 = t2.col3)
union 
select col1, col2, col3
from tmp2 t2
where exists (select 1
              from tmp1 t1
              where t1.col2 = t2.col2 or t1.col3 = t2.col3)

Results:

|  col1 | col2 | col3 |
|-------|------|------|
| test1 |  abc |  def |
| test2 |  aaa |  bbb |
| test1 |  abc |      |
| test2 |  ccc |  bbb |

Upvotes: 0

Hogan
Hogan

Reputation: 70528

I can't tell from your example what columns you want to join but to solve the "null" problem you do it like this

SELECT *
FROM T1
JOIN T2 ON COALESCE(T1.COL1,T2.COL1) = T2.COL1
         AND COALESCE(T1.COL2,T2.COL2) = T2.COL2

If T1.COL1 or T1.COL2 are null it will use the value of the table it is joining to. This allows null to be a "wildcard".

or if T2 is the table with nulls

SELECT *
FROM T1
JOIN T2 ON T1.COL1 = COALESCE(T2.COL1,T1.COL1)
         AND T1.COL2 = COALESCE(T2.COL2,T1.COL2)

Upvotes: 1

Related Questions