SQL LEFT JOIN with conditional CASE statements

Question

Hopefully this is a quickie

SELECT *
FROM T
left JOIN J ON 
  CASE
    WHEN condition1 THEN 1 --prefer this option even if CASE2 has a value
    WHEN condition2 THEN 2
    ELSE 0
  END = 1 (edit: but if 1 does not satisfy, then join on 2)

Both cases return results, but I want THEN 1 to supersede THEN 2 and be the lookup priority

Can I have SQL do something like join on max(CASE)?

Basically I am trying to duplicate a nested INDEX/MATCH from Excel

edit: what i am hearing is that the Case should stop at the first returned TRUE, but it doesn't behave like that when i test

SELECT *
FROM T
left JOIN J ON 
  CASE
    WHEN condition1 THEN 1 --prefer this option even if CASE2 has a value
    WHEN condition2 THEN 1
    ELSE 0
  END = 1

it seems to prefer the 2nd THEN 1 sometimes, but not always... is there anything i am missing that would cause this behavior?

Answer 1

It doesn't matter which of the conditions causes the rows to match in a join. There are legitimate reasons to use a case expression in a join but I think you just want to or your conditions and then use the case expression to output a ranked reason for the match.

SELECT *, CASE WHEN <condition1> THEN 1 WHEN <condition2> THEN 2 END as match_code
FROM T LEFT OUTER JOIN J ON <condition1> or <condition2>

I don't know what to picture regarding the "nested INDEX/MATCH" from Excel. If I'm on the wrong track above then perhaps you're looking for a nested case expression?

Now if your conditions will have matches across different rows and you only want to keep one then...

WITH matches AS (
    SELECT *, CASE WHEN <condition1> THEN 1 WHEN <condition2> THEN 2 END AS match_code
    FROM T LEFT OUTER JOIN J ON <condition1> OR <condition2>
), ranked as (
    SELECT *, MIN(match_code) OVER (PARTITION BY ???) AS keeper
    FROM matches
)
SELECT ...
FROM ranked
WHERE match_code = keeper