How to join same table on multiple columns?-CodePudding

What would be the best way to JOIN a table based on whether join column is NULL or not.

I need to JOIN table #main with table #sub.

For example:

if ID column in table #main is NULL then join on column LocationID = #sub.LocationID

if ID and LocationID columns in table #main is NULLs then join on column UserID = #sub.UserID

create table #main (ID int, locationID varchar(50), UserID varchar(50))
insert into #main values (1,'Loc1',NULL),
                         (2,NULL,'User1'),
                         (NULL,'Loc1','User1'),
                         (4,'Loc1',NULL),
                         (5,NULL,'User1'),
                         (NULL,'Loc1','User1')
--select * from #main


select *
from #main m
-- if m.ID is not null then join on m.ID  
-- OR if m.ID is null then join on m.locationID = s.LocationID  
-- OR if m.ID is null and m.LocationID is null then join on  m.UserID = s.UserID
    left join #sub s ON  m.ID = s.ID OR m.locationID = s.LocationID OR m.UserID = s.UserID

Thank you

CodePudding user response：

Something like this should work. Not sure about performance...

Select * From #Main m 
    left Join #sub s on 
    (m.id is null and m.locationid is null and m.userId = s.userID) 
    or (m.id is not null and m.locationid is null and m.locationid = s.locationId) 
    or (m.id is not null and m.locationid is not  null and m.id = s.id)

CodePudding user response：

Have a query for each condition and union the results, something like

select * 
from #main m
left join #sub s on s.locationId = m.LocationId
where m.Id is null and m.locationId is not null
union all
select * 
from #main m
left join #sub s on s.UserId - m.UserId
where m.Id is null and m.locationId is null

CodePudding user response：

I'm not sure whether you want join or left join (you referred to both in your question), so modify the code below as fits your needs:

select  ...
from    #main  m
join    #sub   s  on m.id = s.id
                     or (m.id is null and and m.locationId = s.locationId)
                     or (m.id is null and m.locationId is null and m.userId = s.userId)

I have used a feature of null here to shorten the syntax: null is not equal to anything. It's not even equal to null. So when doing the join on the m.id column, it might seem like you have to do this:

on m.id is not null and m.id = s.id

But if m.id is null, then it will never equal s.id, because null is not equal to anything. And so you can just do

on m.id = s.id

Because that condition can only be met when m.id is not null. I have used a similar dirty trick for the locationId join condition.

However, joins containing or conditions are often very slow, and it is sometimes helpful to explain to SQL Server that this same problem can be phrased in terms of union operations where the joins contain no or operations, only and operations:

select    ...
from      #main  m
join      #sub   s on m.id = s.id
union all
select    ...
from      #main  m
join      #sub   s on m.id is null 
                      and m.locationId = s.locationId
union all
select    ...
from      #main  m
join      #sub   s on m.id is null 
                      and m.locationId is null 
                      and m.userId = s.userId

CodePudding user response：

as an alternative to join, this can be done using outer apply with a subquery in which the matching condition is implemented via case when like this:

Select *
From #main As m Outer Apply 
(Select * From #sub As s 
 Where 1 = Case When m.ID Is Not Null And m.ID=s.ID Then 1
                When m.ID Is Null And m.LocationID Is Not Null And m.locationID=s.locationID Then 1
                When m.ID Is Null And m.LocationID Is Null And m.UserID Is Not Null And m.UserID=s.UserID Then 1
                Else 0
           End) As t
Order by m.ID, m.locationID, m.UserID