Case within join's "ON" caluse

Question

I'm trying to do something like this...

SELECT lc.Location, count(h.empID) as 'Count', lu.LBL
FROM swam.lookup lu
    LEFT JOIN swam.empTable h
    ON CASE
        WHEN lu.sac is null 
            THEN lu.POS = h.POS
        ELSE (
            lu.POS = h.POS
            and
            lu.sac = h.sac)
    INNER JOIN swam.Locations lc
        ON h.LocationID = lc.LocationID
GROUP BY lc.Location, lu.LBL
ORDER BY lc.Location

So, if lu.sac is null, then only join the tables on one common column. If it's not null, then it has to use both POS and SAC to join.

Is this possible?

score 4 · Accepted Answer · answered Oct 04 '16 at 11:01

4

Don't use case. Just use direct boolean logic:

FROM swam.lookup lu LEFT JOIN
     swam.empTable h
     ON lu.POS = h.POS AND
        (lu.sac is null OR lu.sac = h.sac) INNER JOIN
     swam.Locations fs
     ON h.LocationID = lc.LocationID

As a bonus, the database engine can also table advantage of an index on pos in either (or both) tables.

answered Oct 04 '16 at 11:01

Gordon Linoff

1,242,037
58
646
786

Is there any performance degradation when we put OR condition in Join or Where Condition in SQL Server i.e. non-sargable query? Refer below link :- https://en.wikipedia.org/wiki/Sargable – RGS Oct 05 '16 at 05:17
1

@RGS . . . If the query would otherwise use indexes, then there is likely to be a performance degradation. – Gordon Linoff Oct 06 '16 at 03:24

score 1 · Answer 2 · answered Oct 04 '16 at 11:13

    SELECT lc.Location, count(h.empID) as 'Count', lu.LBL
FROM swam.lookup lu
    LEFT JOIN swam.empTable h
    ON  (lu.sac is null  AND lu.POS = h.POS) OR  
                   not(lu.sac is null)  AND lu.POS = h.POS   and lu.sac = h.sac
    INNER JOIN swam.Locations fs
        ON h.LocationID = lc.LocationID
GROUP BY lc.Location, lu.LBL
ORDER BY lc.Location

paparazzo · Answer 3 · 2016-10-04T11:41:37.163

1

SELECT lc.Location, count(h.empID) as 'Count', lu.LBL
FROM swam.lookup lu
LEFT JOIN swam.empTable h
  ON lu.POS = h.POS
 AND isnull(lu.sac, h.sac) = h.sac    
INNER JOIN swam.Locations fs
   ON h.LocationID = lc.LocationID  // this looks wrong lc is not defined
GROUP BY lc.Location, lu.LBL
ORDER BY lc.Location

and I think that last join will turn the left join intro and inner join

edited Oct 04 '16 at 11:41

answered Oct 04 '16 at 11:33

paparazzo

44,497
23
105
176

my bad, lc is supposed to be lu – blacksaibot Oct 04 '16 at 11:45
my bad. fs should have been lc – blacksaibot Oct 19 '16 at 14:28

Case within join's "ON" caluse

3 Answers3