user1955583 user1955583 - 1 month ago 19
SQL Question

SQL Left Outer Join acting like Inner Join

I am trying to do a left outer join on two tables (well, an inline view and a table).
What I want to happen is to list all the grads (I know there are 3815 DISTINCT Grads) with any of their enrolments (there could be 0 or n enrolments). What I'm getting is only a list of the grads that have enrolments (3649 DISTINCT students). I'm not sure where I'm going wrong with not getting all the rows from the grad 'view' (I don't have create view privs so this is my workaround).

This is my code:

SELECT C.*, D.FREEZE_EVENT, D.ACADEMIC_PERIOD, D.CAMPUS, D.COLLEGE, D.COLLEGE_DESC,D.MAJOR, D.MAJOR_DESC , D.STUDENT_RATE
FROM
(SELECT A.STUDENT_LEVEL_DESC, A.CAMPUS, A.CAMPUS_DESC, A.COLLEGE, A.COLLEGE_DESC, A.MAJOR_DESC, A.MAJOR, A.DEGREE_DESC, A.PERSON_UID, A.ID, A.NAME,
A.OUTCOME_GRADUATION_DATE, A.STATUS, A.GRAD_YEAR, A.TRAINING_LOCATION, B.CITIZENSHIP_TYPE
FROM ACAD_OUTOCME A, PERSON_DETAIL B
WHERE A.STUDENT_LEVEL IN ('02','03') AND A.GRAD_YEAR = '2015' AND A.FREEZE_EVENT = '10TH_SEP2016' AND B.FREEZE_EVENT = '10TH_SEP2016'
AND A.ID = B.ID) C
LEFT OUTER JOIN ACAD_STUDY D ON
C.CAMPUS = D.CAMPUS
AND C.COLLEGE = D.COLLEGE
AND C.MAJOR = D.MAJOR
AND C.PERSON_UID = D.PERSON_UID
WHERE D.FREEZE_EVENT = '10TH_SEP2016'
ORDER BY C.NAME


Any suggestions? I'm using Toad Data Point. I'm also the loan developer at work, so I don't have anyone I can ask to help out with this, and google has failed me.

Thanks!

Answer

Move your WHERE condition to the ON condition:

Select  C.*
      , D.FREEZE_EVENT
      , D.ACADEMIC_PERIOD
      , D.CAMPUS
      , D.COLLEGE
      , D.COLLEGE_DESC
      , D.MAJOR
      , D.MAJOR_DESC
      , D.STUDENT_RATE
From    (Select A.STUDENT_LEVEL_DESC
              , A.CAMPUS
              , A.CAMPUS_DESC
              , A.COLLEGE
              , A.COLLEGE_DESC
              , A.MAJOR_DESC
              , A.MAJOR
              , A.DEGREE_DESC
              , A.PERSON_UID
              , A.ID
              , A.NAME
              , A.OUTCOME_GRADUATION_DATE
              , A.STATUS
              , A.GRAD_YEAR
              , A.TRAINING_LOCATION
              , B.CITIZENSHIP_TYPE
         From   ACAD_OUTOCME A
         Join   PERSON_DETAIL B  On   A.ID = B.ID
         Where  A.STUDENT_LEVEL In ('02', '03')
                And A.GRAD_YEAR = '2015'
                And A.FREEZE_EVENT = '10TH_SEP2016'
                And B.FREEZE_EVENT = '10TH_SEP2016'
        ) C
Left Outer Join ACAD_STUDY D
        On C.CAMPUS = D.CAMPUS
           And C.COLLEGE = D.COLLEGE
           And C.MAJOR = D.MAJOR
           And C.PERSON_UID = D.PERSON_UID
           And D.FREEZE_EVENT = '10TH_SEP2016'
Order By C.NAME;

The WHERE clause is evaluated after the OUTER JOIN, which would cause it to filter out the NULL records from the LEFT JOIN. So, having the right-hand table of a LEFT JOIN in the WHERE clause will effectively transform the OUTER JOIN into an INNER JOIN.