JOIN Optimization with CASE expressions in WHERE-CodePudding

The 2 tables below will pull a world of data from details_2 table but will join to the fields based on different reason coded in #exceptions table.

Is there a better way to write the below query?

Select
a.Fieldname1,
a.Fieldname2,
a.fieldname3
from 
#Exceptions a, [DETAILS_2] b
where 

(case when a.REASON_CD = 'LOBMKT' then CONCAT(a.LOB_CD,a.GEO_MARKET) end = concat(b.LOB_CD,b.GEO_MARKET)) or
(case  when a.REASON_CD = 'LOB' then a.LOB_CD end  = b.LOB_CD) or
(case  when a.REASON_CD = 'CAT' then a.CTRCT_CAT_CD end  = b.CTRCT_CAT_CD) or
(case  when a.REASON_CD = 'LOBST' then CONCAT(a.LOB_CD,a.[derived_GEO_MARKET]) end = concat(b.LOB_CD,b.GEO_MARKET))

CodePudding user response：

I think I would go for this, howver not able to test anything without sample data and desired output like Cetin also mentiones.

I would go for the UNION ALL, as the data should be unique in all different statements. So SQL does not need to verify that (with costing more CPU)

SELECT
     a.Fieldname1,
     a.Fieldname2,
     a.fieldname3
FROM #Exceptions a
     JOIN [DETAILS_2] b ON a.REASON_CD = 'LOBMKT'
                           AND a.LOB_CD = b.LOB_CD
                           AND a.GEO_MARKET = b.GEO_MARKET
UNION ALL
SELECT
     a.Fieldname1,
     a.Fieldname2,
     a.fieldname3
FROM #Exceptions a
     JOIN [DETAILS_2] b ON a.REASON_CD = 'LOB'
                           AND a.LOB_CD = b.LOB_CD
UNION ALL
SELECT
     a.Fieldname1,
     a.Fieldname2,
     a.fieldname3
FROM #Exceptions a
     JOIN [DETAILS_2] b ON a.REASON_CD = 'CAT'
                           AND a.CTRCT_CAT_CD = b.CTRCT_CAT_CD
UNION ALL
SELECT
     a.Fieldname1,
     a.Fieldname2,
     a.fieldname3
FROM #Exceptions a
     JOIN [DETAILS_2] b ON a.REASON_CD = 'LOBST'
                           AND a.LOB_CD = b.LOB_CD
                           AND a.[derived_GEO_MARKET] = b.GEO_MARKET;

CodePudding user response：

(Beware you are doing a cross join)

SELECT a.Fieldname1,
       a.Fieldname2,
       a.fieldname3
FROM #Exceptions a,
     [DETAILS_2] b
WHERE (
          a.REASON_CD = 'LOBMKT'
          AND a.LOB_CD = b.LOB_CD
          AND a.GEO_MARKET = b.GEO_MARKET
      )
      OR
      (
          a.REASON_CD = 'LOB'
          AND a.LOB_CD = b.LOB_CD
      )
      OR
      (
          a.REASON_CD = 'CAT'
          AND a.CTRCT_CAT_CD = b.CTRCT_CAT_CD
      )
      OR
      (
          a.REASON_CD = 'LOBST'
          AND a.LOB_CD = b.LOB_CD
          AND a.[derived_GEO_MARKET] = b.GEO_MARKET
      );

CodePudding user response：

This approach is sargable and easier to understand what it is doing:

Select a.Fieldname1, a.Fieldname2, a.fieldname3
from #Exceptions a
inner join [DETAILS_2] b on a.LOB_CD = b.LOB_CD and a.GEO_MARKET = b.GEO_MARKET
where a.REASON_CD = 'LOBMKT'
UNION ALL
Select a.Fieldname1, a.Fieldname2, a.fieldname3
from #Exceptions a
inner join [DETAILS_2] b on a.LOB_CD = b.LOB_CD
where a.REASON_CD = 'LOB'
UNION ALL
Select a.Fieldname1, a.Fieldname2, a.fieldname3
from #Exceptions a
inner join [DETAILS_2] b on a.CTRCT_CAT_CD = b.CTRCT_CAT_CD
where a.REASON_CD = 'CAT'
UNION ALL
Select a.Fieldname1, a.Fieldname2, a.fieldname3
from #Exceptions a
inner join [DETAILS_2] b on a.LOB_CD = b.LOB_CD and a.[derived_GEO_MARKET] = b.GEO_MARKET
where a.REASON_CD = 'LOBST'