Page History

...

Certain tables will always contain student data. For example, a student would not be a student without an academic program, which is stored in the table ACAD_PROG. Likewise, a student will always have at least one plan, which is stored in the table ACAD_PLAN. It is impossible to be a student without data in both the ACAD_PROG and ACAD_PLAN tables. Because of this certainty, these two tables should be joined with a standard join.

The code for this isfollowing code illustrate the Standard Join:

Select (fields) from ACAD_PROG PROG
	, ACAD_PLAN PLAN
	Where PROG.EMPLID = PLAN.EMPLID
	And	PROG.ACAD_CAREER = PLAN.ACAD_CAREER
	And PROG.STDNT_CAR_NBR = PLAN.STDNT_CAR_NBR
	And PROG.EFFDT = PLAN.EFFDT
	And	PROG.EFFSEQ = PLAN.EFFSEQ

Some tables may or may not contain data. An example of this is the subplan table, ACAD_SUBPLAN. A student can have one or more subplans but it is not mandatory. Therefore, ACAD_SUBPLAN should be joined to the plan, ACAD_PLAN (its owning table), using an outer join. Continuing the code above:

The following code illustrate the Outer Join:

Select (fields) from ACAD_PROG PROG
	, ACAD_PLAN PLAN
	, ACAD_SUBPLAN SPLAN
	Where PROG.EMPLID 		= PLAN.EMPLID
	And	PROG.ACAD_CAREER 	= PLAN.ACAD_CAREER
	And PROG.STDNT_CAR_NBR 	= PLAN.STDNT_CAR_NBR
	And PROG.EFFDT 			= PLAN.EFFDT
	AndPROG.EFFSEQ 			= PLAN.EFFSEQ 
	And PLAN.EMPLID 		= SPLAN.EMPLID (+) 
	And PLAN.ACAD_CAREER 	= SPLAN.ACAD_CAREER (+) 
	And PLAN.STDNT_CAR_NBR 	= SPLAN.STDNT_CAR_NBR (+) 
	And PLAN.EFFDT 			= SPLAN.EFFDT (+) 
	And PLAN.EFFSEQ 		= SPLAN.EFFSEQ (+) 
	And PLAN.ACAD_PLAN 		= SPLAN.ACAD_PLAN (+) 

Note
NOTE: The plus sign is an indicator of an outer join. All keys must be joined in the same manner. If even one key between outer-joined tables is

...

To Review: Use a standard join between two tables which contain data; use an outer join between two tables, one of which may or may not contain data.