CODEWITHSUNDEEP
sqloracle-databaseselectleft-joinwhere-clause
mkazma
mkazma

Reputation: 582

Alternative way to left join in SQL ORACLE DB

if I run the query below query:

SELECT day.M_CMP_TYPO AS M_CMP_TYPO,
        day.M_SPTCV as M_SPTCV,
       day.M_CNT_VS2 AS M_CNT_VS2,
       day.M_CNT_ORG AS M_CNT_ORG,
       day.M_PL_CGR2 AS M_PL_CGR2,
       day.M_PL_CGU2 AS M_PL_CGU2,
       day.M_PL_CSFI2 AS M_PL_CSFI2,
       day.M_PL_FTFI2 AS M_PL_FTFI2,
       day.M_PL_RVR2 AS M_PL_RVR2,
       day.M_PL_RVU2 AS M_PL_RVU2,
       day.M_TRN_FMLY AS M_TRN_FMLY,
       day.M_TRN_GRP AS M_TRN_GRP,
       day.M_TRN_TYPE AS M_TRN_TYPE,
       day.M_CNT_VS2 AS M_CNT_VS2,
       day.M_C_CUR_PL AS M_C_CUR_PL,
       day.M_INSTRLABEL AS M_INSTRLABEL,
       day.M_NB AS M_NB,
       day.M_PL_INSCUR AS M_PL_INSCUR,
       day.M_TP_CNTRPLB AS M_TP_CNTRPLB,
       day.M_TP_PFOLIO AS M_TP_PFOLIO,
       day.M_ECO_PL AS M_ECO_PL,
       day.M_CNT_ID AS M_CNT_ID,
       day.M_ECO_PL_USD AS M_ECO_PL_USD,
       day.M_POS_CURR2 AS M_POS_CURR2,
       day.M_CURR2 AS M_CURR2,
       day.M_TP_QTYEQ AS M_TP_QTYEQ,
       day.M_TP_UQTYEQ AS M_TP_UQTYEQ,
       day.M_TP_LQTY32 AS M_TP_LQTY32,
       day.M_TP_UQTY AS M_TP_UQTY,


     --day.M_ECO_PL - daily.M_ECO_PL AS DAILY_VAR,
         --day.M_ECO_PL - month.M_ECO_PL AS MTD,
     day.M_ECO_PL - year.M_ECO_PL AS YTD,



--day.M_SPTCV * (day.M_ECO_PL - daily.M_ECO_PL) as DAILY_VAR_USD,
--day.M_SPTCV * (day.M_ECO_PL - month.M_ECO_PL) as MTD_USD,
day.M_SPTCV * (day.M_ECO_PL - year.M_ECO_PL) as YTD_USD



  FROM RT_PLVAR_REP day
  LEFT JOIN RT_PLVAR_REP year ON day.M_NB = year.M_NB
    --LEFT JOIN RT_PLVAR_REP month ON day.M_NB = month.M_NB
  --LEFT JOIN RT_PLVAR_REP daily ON day.M_NB = daily.M_NB

WHERE day.M_REF_DATA = 18
AND   year.M_REF_DATA = 20
--AND   month.M_REF_DATA = 0
--AND   daily.M_REF_DATA = 0

it returns the expected behavior, that means it returns 27 rows knowing that year.M_REF_DATA = 20 exists in the DB. else if I run this one I have no values. Since the M_REF_DATA=0 doesn't exist in the table I was expecting that this query returns 27 rows but the related columns should return null, but that's not the case.

I have tried to replace Where with AND it didn't work either. It returned 1728 rows which is a wrong answer, it should return 11. My question is why the left join isn't working as am expecting ?

SELECT day.M_CMP_TYPO AS M_CMP_TYPO,
            day.M_SPTCV as M_SPTCV,
           day.M_CNT_VS2 AS M_CNT_VS2,
           day.M_CNT_ORG AS M_CNT_ORG,
           day.M_PL_CGR2 AS M_PL_CGR2,
           day.M_PL_CGU2 AS M_PL_CGU2,
           day.M_PL_CSFI2 AS M_PL_CSFI2,
           day.M_PL_FTFI2 AS M_PL_FTFI2,
           day.M_PL_RVR2 AS M_PL_RVR2,
           day.M_PL_RVU2 AS M_PL_RVU2,
           day.M_TRN_FMLY AS M_TRN_FMLY,
           day.M_TRN_GRP AS M_TRN_GRP,
           day.M_TRN_TYPE AS M_TRN_TYPE,
           day.M_CNT_VS2 AS M_CNT_VS2,
           day.M_C_CUR_PL AS M_C_CUR_PL,
           day.M_INSTRLABEL AS M_INSTRLABEL,
           day.M_NB AS M_NB,
           day.M_PL_INSCUR AS M_PL_INSCUR,
           day.M_TP_CNTRPLB AS M_TP_CNTRPLB,
           day.M_TP_PFOLIO AS M_TP_PFOLIO,
           day.M_ECO_PL AS M_ECO_PL,
           day.M_CNT_ID AS M_CNT_ID,
           day.M_ECO_PL_USD AS M_ECO_PL_USD,
           day.M_POS_CURR2 AS M_POS_CURR2,
           day.M_CURR2 AS M_CURR2,
           day.M_TP_QTYEQ AS M_TP_QTYEQ,
           day.M_TP_UQTYEQ AS M_TP_UQTYEQ,
           day.M_TP_LQTY32 AS M_TP_LQTY32,
           day.M_TP_UQTY AS M_TP_UQTY,


         day.M_ECO_PL - daily.M_ECO_PL AS DAILY_VAR,
             day.M_ECO_PL - month.M_ECO_PL AS MTD,
         day.M_ECO_PL - year.M_ECO_PL AS YTD,



    day.M_SPTCV * (day.M_ECO_PL - daily.M_ECO_PL) as DAILY_VAR_USD,
    day.M_SPTCV * (day.M_ECO_PL - month.M_ECO_PL) as MTD_USD,
    day.M_SPTCV * (day.M_ECO_PL - year.M_ECO_PL) as YTD_USD



      FROM RT_PLVAR_REP day
      LEFT JOIN RT_PLVAR_REP year ON day.M_NB = year.M_NB
        LEFT JOIN RT_PLVAR_REP month ON day.M_NB = month.M_NB
      LEFT JOIN RT_PLVAR_REP daily ON day.M_NB = daily.M_NB

    WHERE day.M_REF_DATA = 18
    AND   year.M_REF_DATA = 0
    AND   month.M_REF_DATA = 0
    AND   daily.M_REF_DATA = 0

Upvotes: 0

Views: 560

Answers (2)

Allan
Allan

Reputation: 17429

For the outer joins, you need to move the filter criteria to the join:

  FROM RT_PLVAR_REP day
  LEFT JOIN RT_PLVAR_REP year 
    ON day.M_NB = year.M_NB
       AND year.M_REF_DATA = 0
  LEFT JOIN RT_PLVAR_REP month 
    ON day.M_NB = month.M_NB
       AND month.M_REF_DATA = 0
  LEFT JOIN RT_PLVAR_REP daily 
    ON day.M_NB = daily.M_NB
       AND daily.M_REF_DATA = 0
WHERE day.M_REF_DATA = 18

This tells the database that you only want to enforce that criteria when a row exists.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270341

This is the from and where clauses from the first query:

FROM RT_PLVAR_REP day LEFT JOIN
     RT_PLVAR_REP year
     ON day.M_NB = year.M_NB
WHERE day.M_REF_DATA = 18 AND year.M_REF_DATA = 20

The where clause turns the LEFT JOIN into an INNER JOIN. The value of year.M_REF_DATA is NULL, so it fails the condition.

For a LEFT JOIN, the conditions should go in the ON clause:

FROM RT_PLVAR_REP day LEFT JOIN
     RT_PLVAR_REP year
     ON day.M_NB = year.M_NB AND year.M_REF_DATA = 20
WHERE day.M_REF_DATA = 18

Conditions on the first table should stay in the WHERE.

Upvotes: 5

Related Questions