sql left join on / where

如例子： 對OMS這個項目的例子：

・	実施資材単品テーブルより、指定條件のデータを取得する。取得項目はUI仕様書參照。
手術実施ID＝指定手術実施ID
実施資材単品テーブル.単品コード＝単品マスタ.単品コード and 単品マスタ.償還材料フラグ＝'1'

select
    OPE_CD,
    to_char(CURRENT_DATE,'yyyy/MM/dd') AS CURRENT_DATE,
    PATIENT_ID,
    EXAMINATION_COURSE_NAME,
    to_char(OPE_DAY,'yyyy/MM/dd') as OPE_DAY,
    PHYSIQUE_CD1,
    PHYSIQUE_CD2,
    PATIENT_NAME,
    to_char(PATIENT_BIRTH,'yyyy/MM/dd') as PATIENT_BIRTH,
    to_char(AGE,'9999') as AGE,
    PATIENT_SEX,
    ILLNESS_NAME_1,
    ILLNESS_NAME_2,
    ILLNESS_NAME_3,
    to_char(OPE_START_TIME,'HH24:MI:SS') as OPE_START_TIME,
    to_char(OPE_END_TIME,'HH24:MI:SS') as OPE_END_TIME,
    to_char(REQUIRETIME,'HH24:MI:SS') as REQUIRETIME,
    to_char(ANESTHESIA_START_TIME,'HH24:MI:SS') as ANESTHESIA_START_TIME,
    to_char(ANESTHESIA_END_TIME,'HH24:MI:SS') as ANESTHESIA_END_TIME ,
    to_char(ANESTHESIATIME,'HH24:MI:SS') as ANESTHESIATIME,
    ARTTYPE_NAME_1,
    ARTTYPE_NAME_2,
    ARTTYPE_NAME_3,
    ANESTHESIA_NAME_1,
    ANESTHESIA_NAME_2,
    ANESTHESIA_NAME_3,
    emst.MATERIAL_DIVISION_FLAG ,
    sm.JAN_CD AS SKU_CD,
    
    CASE WHEN sm.ABBREVIATED IS NULL OR TRIM(sm.ABBREVIATED) = ''
         THEN sm.ARTICLENAME
         ELSE sm.ABBREVIATED
    END AS ARTICLENAME,
               
    sm.STANDARD,
    sm.MAKER,
    sum (emst.use_amount) as SUMUSEAMOUNT,
    um.UNIT_NAME,
               
    case
       when (emst.MATERIAL_DIVISION_FLAG <> '5' and emst.MATERIAL_DIVISION_FLAG <> '6')
       then
           '0'
                   when (emst.MATERIAL_DIVISION_FLAG = '6' and sm.sku_division_flag = '2')
       then
           '9999'
                   else sm.sku_division_flag
                end  as sku_division_flag,

               emst.sku_cd as _sku_cd,
                emst.set_cd as SETCD,
                sm.repaymentmaterials_flag

   from

       ( select
      oet.ope_cd AS OPE_CD,
      CURRENT_DATE AS CURRENT_DATE,
      oet.patient_id AS PATIENT_ID,
      (  select
        ecm.examination_course_name
       from
       examination_course_mst ecm
       where
       ecm.examination_course_cd = oet.PERFORM_DOC_EXAMINATION_COURSE_CD
      ) AS EXAMINATION_COURSE_NAME,
      oet.ope_day AS OPE_DAY,
      (    select
           USER_NAME
           FROM
           user_mst
           where
           oet.PERFORM_DOC_ID_1 = USER_CD  )AS PHYSIQUE_CD1,
      oet.physique_cd_2  AS PHYSIQUE_CD2,
      oet.patient_name  AS PATIENT_NAME,
      oet.patient_birth AS PATIENT_BIRTH,
      (select (CURRENT_DATE - to_date(oet.patient_birth,'yyyy'))/365) AS AGE,
      oet.patient_sex as PATIENT_SEX,
      oet.ILLNESS_NAME_1,
      oet.ILLNESS_NAME_2,
      oet.ILLNESS_NAME_3,
      oet.OPE_START_TIME,
      oet.OPE_END_TIME,
      
      case

when (oet.ope_start_nextday_check is null or oet.ope_start_nextday_check ='0')    and       oet.ope_end_nextday_check = '1'
 then
        oet.ope_end_time-oet.ope_start_time + interval '24 hours'
 else

        oet.ope_end_time-oet.ope_start_time

     end AS REQUIRETIME,

     oet.ANESTHESIA_START_TIME,
     oet.ANESTHESIA_END_TIME,
     case

           when (oet.anesthesia_start_nextday_check is null or oet.anesthesia_start_nextday_check = '0') and oet.anesthesia_end_nextday_check = '1'

           then   
                 oet.anesthesia_end_time - oet.anesthesia_start_time + interval '24 hours'
           else
                  oet.anesthesia_end_time - oet.anesthesia_start_time
     end AS ANESTHESIATIME,

      oet.ARTTYPE_NAME_1,
      oet.ARTTYPE_NAME_2,
      oet.ARTTYPE_NAME_3,
      oet.ANESTHESIA_NAME_1,
      oet.ANESTHESIA_NAME_2,
      oet.ANESTHESIA_NAME_3,
      oet.ope_effect_id AS ope_effect_id
     from
      ope_effect_table oet
     where
     oet.ope_cd='0000000063'                    and
        oet.ope_effect_id = '00000000053'
    ) as aa

   left join
              (
              select * from effect_material_sku_table where use_amount <> 0

               and
               (del_flag = '0' or del_flag is null)
               and

                sku_cd != ''

                and

               sku_cd is not null
             

               ) emst

           on aa.ope_effect_id = emst.ope_effect_id

           left join

         (select * from sku_mst
          where sku_cd is not null and

          repaymentmaterials_flag ='1' and

          sku_cd != ''

                and

               sku_cd is not null

          )sm

         on emst.sku_cd =sm.sku_cd

         left join unit_mst um  on sm.unit_cd = um.unit_cd

        group by
                 aa.ope_cd,
                 aa.ope_day,
                 aa.patient_id,
                 aa.examination_course_name,
                 aa.physique_cd1,
                 aa.physique_cd2,
                 aa.patient_name,
                 aa.patient_birth,
                 aa.patient_sex,
                 aa.illness_name_1,
                 aa.illness_name_2,
                 aa.illness_name_3,
                 aa.ope_start_time,
                 aa.ope_end_time,
                 aa.anesthesia_start_time,
                 aa.anesthesia_end_time,
                 aa.arttype_name_1,
                 aa.arttype_name_2,
                 aa.arttype_name_3,
                 aa.anesthesia_name_1,
                 aa.anesthesia_name_2,
                 aa.anesthesia_name_3,
                 ARTICLENAME,
                 sm.standard,
                 sm.maker,
                 um.unit_name,
                 emst.material_division_flag,
                 sm.JAN_CD,
                 sm.ABBREVIATED,
                aa.age,
                aa.requiretime,
                aa.anesthesiatime,
                sku_division_flag
                ,_sku_cd
                ,SETCD
                ,sm.repaymentmaterials_flag
      
      
      order by _sku_cd,SETCD

 

 這裡有三個內部子查詢 aa ,emst ,和 sm ,還有一個表是單位表 unit_mst  ，因為 aa 這個查詢總會有結果（他指的是患者的資訊），所以要求無論 emst 或sm 抑或 unit_mst  即使沒有結果，至少要查出 aa  這個表的患者的記錄，我原想會查詢出符合where條件的結果，但卻不是這個樣子，經過尋找，發現有 repaymentmaterials_flag ！= ‘1’ 的記錄也被查處來，這是為什麼呢？ 原來 repaymentmaterials_flag = ‘1’ 是在 sm這個子查詢中的條件，又因為aa與 emst  進行的左查詢沒有限制 repaymentmaterials_flag ！= ‘1’  （也不可能限制，因為aa與 emst  均沒有這個欄位），所以就會有查出這樣的單品：如它的sku_cd = '003' ,在 單品表中差得sku_cd = '003'

但 repaymentmaterials_flag ！= ‘1’   ，因為是左查所以會列出。