Oracle SQL: Outer Join Not Working As Expected

Question

I am joining two tables, T_ADMSTU_PROG_ENROLL (renamed prog_enroll) and T_ADMGRAD_STUDENT (renamed grad). I would expect the result to have the following columns:

TERM_CODE, STU_INST_UID, MAJOR_DESC, MAJOR_CIP, B_GRADUATION_TERM

keyed on prog_enroll.STU_INST_UID = grad.STU_INST_UID and prog.enroll TERM_CODE = B_GRADUATION_TERM and include all rows from both tables. I am, however, missing rows from T_ADMGRAD_STUDENT where the value B_GRADUATION_TERM is not in the TERM_CODE. What am I doing wrong?

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;

Answer 1

Perhaps changing your JOIN condition slightly might help if you want the value B_GRADUATION_TERM that doesn't match TERM_CODE without creating a Cartesian join :

From:

grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;

To:

 (grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);

As shown completely here:

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
(grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);