Conditions with Outer Join

Question

In a where clause I want to return all records with an 'E' at the end of the CRSE_NUMB or all records that have an 'AS' code in ASCD_CODE.

Is it all right to add this OR condition in the where statement for the SLRRASG table if it has an outer join?

select distinct
      SPRIDEN.SPRIDEN_PIDM PIDM,
      SFRSTCR.SFRSTCR_TERM_CODE Term_Code, 
      SFRSTCR.SFRSTCR_PTRM_CODE,
      SPRIDEN.SPRIDEN_LAST_NAME LNAME, 
      SPRIDEN.SPRIDEN_FIRST_NAME FNAME, 
      SPRIDEN.SPRIDEN_ID SID,
      SGBSTDN.SGBSTDN_COLL_CODE_1,
     -- decode(SGBSTDN.SGBSTDN_COLL_CODE_1, 'YC', 'Yale_College', 'SU', 'Visiting_Student') Student_Type, 
   --   SFRSTCR.SFRSTCR_RSTS_CODE, 
   --   SGBSTDN.SGBSTDN_LEVL_CODE LVL_Code, 
   --   SFRSTCR.SFRSTCR_LEVL_CODE CRS_LVL, 
       SLRRASG.SLRRASG_ASCD_CODE Housing_Status,
    -- SLRRASG.SLRRASG_BEGIN_DATE, 
    -- SLRRASG.SLRRASG_END_DATE,
      SCBCRSE.SCBCRSE_SUBJ_CODE, 
      SCBCRSE.SCBCRSE_CRSE_NUMB,
      SFRSTCR.SFRSTCR_CRN CRN,
    --  SCBCRSE.SCBCRSE_TITLE,
    --  SFRSTCR.SFRSTCR_ADD_DATE,
    --  SCBCRSE.SCBCRSE_OTH_HR_LOW as "Credit",
    --  SFRSTCR.SFRSTCR_BILL_HR, 
    --  SFRSTCR.SFRSTCR_CREDIT_HR
    --SFRSTCR.SFRSTCR_CREDIT_HR, 
    listagg(distinct goremal.goremal_email_address, ',')
from
  SATURN.SGBSTDN
  join SATURN.SFRSTCR
    on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
      and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
  join SATURN.SPRIDEN
    on SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
  left join SATURN.SLRRASG
      on SGBSTDN.SGBSTDN_PIDM = SLRRASG.SLRRASG_PIDM
         and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SLRRASG.SLRRASG_TERM_CODE
         -- and  SLRRASG.SLRRASG_ASCD_CODE = 'AS'
 join SATURN.SSBSECT
  on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
    and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
 join SATURN.SCBCRSE
  on SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
    and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
  left join GENERAL.GOREMAL
   on SPRIDEN.SPRIDEN_PIDM = GOREMAL.GOREMAL_PIDM
    and GOREMAL.GOREMAL_EMAL_CODE = 'HAPP'
where
  SPRIDEN.SPRIDEN_ID like '9%'
  and  SFRSTCR.SFRSTCR_TERM_CODE = '202302'
  and SGBSTDN.SGBSTDN_COLL_CODE_1 = 'SU'
  and SPRIDEN.SPRIDEN_CHANGE_IND is null
  and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
  and SSBSECT.SSBSECT_SSTS_CODE in ('A', 'E', 'O')
  and SFRSTCR.SFRSTCR_PTRM_CODE in ('H4B', 'H5A', 'H5B', 'H10', 'H8', 'HSL', 'HND', 'HE3', 'HEL', 'H6', 'HFY', 'HWY')
  and (SCBCRSE.SCBCRSE_CRSE_NUMB not like '%E' or 
      SLRRASG.SLRRASG_ASCD_CODE = 'AS')
 --  and SCBCRSE.SCBCRSE_CRSE_NUMB not like '%E'
  and SCBCRSE.SCBCRSE_EFF_TERM = (
    select max(scbcrse_eff_term) from saturn.scbcrse xppd
    where
    xppd.scbcrse_subj_code = scbcrse.scbcrse_subj_code
    and
    xppd.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb and xppd.scbcrse_eff_term <= ssbsect_term_code)
group by
   SPRIDEN.SPRIDEN_PIDM,
  SFRSTCR.SFRSTCR_TERM_CODE, 
  SFRSTCR.SFRSTCR_PTRM_CODE,
  SPRIDEN.SPRIDEN_LAST_NAME, 
  SPRIDEN.SPRIDEN_FIRST_NAME, 
  SPRIDEN.SPRIDEN_ID,
  SGBSTDN.SGBSTDN_COLL_CODE_1,
   SLRRASG.SLRRASG_ASCD_CODE,
 SLRRASG.SLRRASG_BEGIN_DATE, 
 SLRRASG.SLRRASG_END_DATE,
  SCBCRSE.SCBCRSE_SUBJ_CODE, 
  SCBCRSE.SCBCRSE_CRSE_NUMB,
 SFRSTCR.SFRSTCR_CRN

`SELECT DISTINCT` combined with `GROUP BY` raises many eye-brows. — jarlh, Apr 10 '23 at 19:05
What do you mean by “ if it is alright”? If your query returns the correct result then it is right, otherwise it is wrong — NickW, Apr 10 '23 at 19:11
[Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/a/55111083/3404097) [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) [mre] [ask] [Help] — philipxy, Apr 11 '23 at 22:16
LEFT/RIGHT JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. FULL gives INNER JOIN ON rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN ON is part of your OUTER JOIN ON. After a LEFT/RIGHT/FULL JOIN a WHERE, INNER JOIN or HAVING needing some column(s) of the right/left/2 [sic] table(s) to be not NULL removes rows with introduced NULLs, leaving only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". Needing a non-NULL right/left column "turns FULL into RIGHT/LEFT". You have that. — philipxy, Apr 11 '23 at 22:21

score 0 · Answer 1 · answered Apr 10 '23 at 19:09

Yes that's perfectly okay to filter in the WHERE clause. Just make sure you include a condition for handling a NULL value due to the outer join if your logic requires it. You may not need it:

and (SCBCRSE.SCBCRSE_CRSE_NUMB not like '%E' or 
     SLRRASG.SLRRASG_ASCD_CODE = 'AS')

If SLRRASG_ASCD_CODE is NULL then the second filter will fail but the first might still succeed, and that may be what you want, in which case you're fine. But if you want to treat NULL as the same as 'AS' then you should add:

and (SCBCRSE.SCBCRSE_CRSE_NUMB not like '%E' or 
     SLRRASG.SLRRASG_ASCD_CODE = 'AS' or
     SLRRASG.SLRRASG_ASCD_CODE IS NULL)

MT0 · Answer 2 · 2023-04-10T22:25:12.207

You want to add the filter to the join condition:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
          AND t2.ascd_code = 'AS'
WHERE  other_conditions

If you use:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
WHERE  t2.ascd_code = 'AS'
AND    other_conditions

Then there must be a matched row for t2 to be able to have an ascd_code that matches the filter and your query will effectively have an INNER JOIN and not an OUTER JOIN so is the same as:

SELECT *
FROM   table1 t1
       INNER JOIN table2 t2
       ON     t1.id = t2.id
WHERE  t2.ascd_code = 'AS'
AND    other_conditions

If you do want to filter in the WHERE clause then you need to check if the row did not match in the OUTER JOIN and you can do that by checking if the columns(s) used in the join condition are NULL:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
WHERE  (t2.id IS NULL OR t2.ascd_code = 'AS')
AND    other_conditions

If you want to filter on multiple conditions and they both must be true if the outer joins are matched then:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
          AND t2.ascd_code = 'AS'
       LEFT OUTER JOIN table3 t3
       ON     t1.id = t3.id
          AND t3.crse_numb NOT LIKE '%E'
WHERE  other_conditions

or, if you want either: t2.ascd_code = 'AS' and t3.crse_numb to be anything; or t2.ascd_code to be anything and t3.crse_numb not end with E then:

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
       LEFT OUTER JOIN table3 t3
       ON     t1.id = t3.id
WHERE  other_conditions
AND    (  t2.ascd_code = 'AS'
       OR t3.crse_numb NOT LIKE '%E' )

Note: This will force either t2 or t3 to always be matched and whichever table is matched will effectively be INNER JOINed and the other table can be OUTER JOINed.

Conditions with Outer Join

2 Answers2