CUNYfirst is a relational database system which means that data is stored in tables that are 'related' to each other by one or more keys. Addresses are stored in one table, names in another, academic plans in another, etc. In order to gather information from various tables to create a meaningful report, it is necessary to join the tables which contain the needed fields. Joins can be done in one of two ways: standard joins and outer joins.

  • Standard joins are used when both joined tables contain data. 
  • Outer joins are used when one of the joined tables may not contain data. If two tables are joined using a standard join and one table is missing data, data from both tables will not appear in the report.

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 following 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: The plus sign (plus) 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 missing it considered as a standard join. Also, an outer join is permissible between not more than two tables.

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.

  • No labels