4.7. The OUTER JOIN
In an equi-inner join, rows without matching values are eliminated from the join result. For example, with the following join, we did not see information on any course that did not have a prerequisite:
SELECT *
FROM Course c, Prereq p
WHERE c.course_number = p.course_number
In some cases, it may be desirable to include rows from one table even if it does not have matching rows in the other table. This is done by the use of an OUTER JOIN. OUTER JOINs are used when we want to keep all the rows from the one table, such as Course, or all the rows from the other, regardless of whether they have matching rows in the other table. In SQL Server, an OUTER JOIN in which we want to keep all the rows from the first (left) table is called a LEFT OUTER JOIN,
and an OUTER JOIN in which we want to keep all the rows from the second table (or right relation) is called the RIGHT OUTER JOIN. The term FULL OUTER JOIN is used to designate the union of the LEFT and RIGHT OUTER JOINs. In the following subsections, we illustrate the LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
4.7.1. The LEFT OUTER JOIN
LEFT OUTER JOINs include all the rows from the first (left) of the two tables, even if there are no matching values for the rows in the second (right) table. LEFT OUTER JOINs are performed in SQL Server using a LEFT OUTER JOIN statement.
 | LEFT JOIN is the same as LEFT OUTER JOIN. The inclusion of the word OUTER is optional in SQL Server SQL, but we will use LEFT OUTER JOIN instead of LEFT JOIN for clarity. |
|
The following is the simplest form of a LEFT OUTER JOIN statement:
SELECT columns
FROM table1 LEFT OUTER JOIN table2
ON table1.column1=table2.column1
For example, if we want to list all the rows in the Course table (the left, or first table), even if these courses do not have prerequisites, we type the following LEFT OUTER JOIN statement:
SELECT *
FROM Course c LEFT OUTER JOIN Prereq p
ON c.course_number = p.course_number
Here the LEFT OUTER JOIN is processed as follows: First, all the rows from the Course table that have course_number equal to the course_number in the Prereq table are joined. Then, when a row (with a course_number) from the Course table (first table) has no match in Prereq table (second table), the rows from the Course table are anyway included in the result set with a row of null values joined to the right side. This means that the courses that do not have prerequisites will get a set of null values for prerequisites. So, the output (result set) of a LEFT OUTER JOIN includes all rows from the left (first) table, which in this case is the Course table with matching Prereq rows where applicable.
| The use of the *= operator for the LEFT OUTER JOIN is considered old syntax, and hence its use is not encouraged. It is prone to ambiguities, especially when joining three or more tables. |
|
The previous query will produce the following 33 rows of output (of which we show the first 13 rows here):
COURSE_NAME COURSE_NUMBER CREDIT_HOURS OFFERING_DEPT COURSE_NUMBER PREREQ
-------------------- ------------- ------------ ------------- ------------- -------
ACCOUNTING I ACCT2020 3 ACCT NULL NULL
ACCOUNTING II ACCT2220 3 ACCT NULL NULL
MANAGERIAL FINANCE ACCT3333 3 ACCT ACCT3333 ACCT2220
ACCOUNTING INFO SYST ACCT3464 3 ACCT NULL NULL
INTRO TO CHEMISTRY CHEM2001 3 CHEM NULL NULL
ORGANIC CHEMISTRY CHEM3001 3 CHEM CHEM3001 CHEM2001
INTRO TO COMPUTER SC COSC1310 4 COSC NULL NULL
TURBO PASCAL COSC2025 3 COSC NULL NULL
ADVANCED COBOL COSC2303 3 COSC NULL NULL
DATA STRUCTURES COSC3320 4 COSC COSC3320 COSC1310
DATABASE COSC3380 3 COSC COSC3380 COSC3320
DATABASE COSC3380 3 COSC COSC3380 MATH2410
OPERATIONS RESEARCH COSC3701 3 COSC NULL NULL
.
.
.
(33 row(s) affected)
Note the nulls added to courses (due to the LEFT OUTER JOIN) like ACCOUNTING I, ACCOUNTING II, ACCOUNTING INFO SYST, and so on, which are the courses (in the Course table) that do not have prerequisites.
4.7.2. The RIGHT OUTER JOIN
RIGHT OUTER JOINs include all the rows from the second (right) of the two tables, even if there are no matching values for the rows in the first (left) table. RIGHT OUTER JOINs are performed in SQL Server using a RIGHT OUTER JOIN statement.
| RIGHT JOIN is the same as RIGHT OUTER JOIN. The inclusion of the word OUTER is optional in SQL Server SQL, but we will use RIGHT OUTER JOIN instead of RIGHT JOIN for clarity's sake. |
|
The following is the simplest form of a RIGHT OUTER JOIN statement:
SELECT columns
FROM table1 RIGHT OUTER JOIN table2
ON table1.fieldcolumn1=table2.column1
As an example, we will redo the previous query from the right side. If we want to list all the rows in the Course table (the right, or second table), even if these courses do not have prerequisites, we may type the following RIGHT OUTER JOIN statement:
SELECT *
FROM Prereq p RIGHT OUTER JOIN Course c
ON p.course_number = c.course_number
Here, the RIGHT OUTER JOIN is processed as follows. First, all the rows from the Prereq table that have course_number equal to the course_number in the Course table are joined. Then, when a row (with a course_number) from the Course table (second table) has no match in the Prereq table (first table), the rows from the Course table are anyway included in the result set with a row of null values joined to the left side. This means that courses that do not have prerequisites will get a set of null values joined to the left side. The output of a RIGHT OUTER JOIN includes all rows from the right (second) table, which in this case is the Course table, producing output similar to that obtained in the previous section.
The output consists of 33 rows (of which the first 13 rows are shown here):
COURSE_NUMBER PREREQ COURSE_NAME COURSE_NUMBER CREDIT_HOURS OFFERING_DEPT
------------- -------- -------------------- ------------- ------------ ------------
NULL NULL ACCOUNTING I ACCT2020 3 ACCT
NULL NULL ACCOUNTING II ACCT2220 3 ACCT
ACCT3333 ACCT2220 MANAGERIAL FINANCE ACCT3333 3 ACCT
NULL NULL ACCOUNTING INFO SYST ACCT3464 3 ACCT
NULL NULL INTRO TO CHEMISTRY CHEM2001 3 CHEM
CHEM3001 CHEM2001 ORGANIC CHEMISTRY CHEM3001 3 CHEM
NULL NULL INTRO TO COMPUTER SC COSC1310 4 COSC
NULL NULL TURBO PASCAL COSC2025 3 COSC
NULL NULL ADVANCED COBOL COSC2303 3 COSC
COSC3320 COSC1310 DATA STRUCTURES COSC3320 4 COSC
COSC3380 COSC3320 DATABASE COSC3380 3 COSC
COSC3380 MATH2410 DATABASE COSC3380 3 COSC
NULL NULL OPERATIONS RESEARCH COSC3701 3 COSC
.
.
.
(33 row(s) affected)
Once again, note the NULLs added to the unmatched rows from the second table due to the use of the RIGHT OUTER JOIN.
4.7.3. The FULL OUTER JOIN
The FULL OUTER JOIN includes the rows that are equi-joined from both tables, plus the remaining rows from the first table and the remaining rows from the second table. NULLs are added to the unmatched rows from both the first and second tables.
The following is the simplest form of a FULL OUTER JOIN statement:
SELECT columns
FROM table1 FULL OUTER JOIN table2
ON table1.column1=table2.column1
If we want to list all the rows for which a connection exists between the Prereq table and the Course table (result of a regular JOIN), and in addition, we want all rows from the Prereq table for which there is no corresponding row in the Course table (LEFT OUTER JOIN), and in addition, we want all rows in the Course table for which there is no corresponding row in the Prereq table (RIGHT OUTER JOIN), we would use the following FULL OUTER JOIN statement:
SELECT *
FROM Prereq p FULL OUTER JOIN Course c
ON p.course_number = c.course_number
We will get 33 rows:
COURSE_NUMBER PREREQ COURSE_NAME COURSE_NUMBER CREDIT_HOURS OFFERING_DEPT
------------- -------- -------------------- ------------- ------------ ------------
NULL NULL ACCOUNTING I ACCT2020 3 ACCT
NULL NULL ACCOUNTING II ACCT2220 3 ACCT
ACCT3333 ACCT2220 MANAGERIAL FINANCE ACCT3333 3 ACCT
NULL NULL ACCOUNTING INFO SYST ACCT3464 3 ACCT
NULL NULL INTRO TO CHEMISTRY CHEM2001 3 CHEM
CHEM3001 CHEM2001 ORGANIC CHEMISTRY CHEM3001 3 CHEM
NULL NULL INTRO TO COMPUTER SC COSC1310 4 COSC
NULL NULL TURBO PASCAL COSC2025 3 COSC
NULL NULL ADVANCED COBOL COSC2303 3 COSC
COSC3320 COSC1310 DATA STRUCTURES COSC3320 4 COSC
COSC3380 COSC3320 DATABASE COSC3380 3 COSC
COSC3380 MATH2410 DATABASE COSC3380 3 COSC
NULL NULL OPERATIONS RESEARCH COSC3701 3 COSC
NULL NULL ADVANCED ASSEMBLER COSC4301 3 COSC
NULL NULL SYSTEM PROJECT COSC4309 3 COSC
COSC5234 COSC3320 ADA - INTRODUCTION COSC5234 4 COSC
NULL NULL NETWORKS COSC5920 3 COSC
NULL NULL ENGLISH COMP I ENGL1010 3 ENGL
ENGL1011 ENGL1010 ENGLISH COMP II ENGL1011 3 ENGL
ENGL3401 ENGL1011 FUND. TECH. WRITING ENGL3401 3 ENGL
NULL NULL TECHNICAL WRITING ENGL3402 2 ENGL
ENGL3520 ENGL1011 WRITING FOR NON MAJO ENGL3520 2 ENGL
NULL NULL CALCULUS 1 MATH1501 4 MATH
NULL NULL CALCULUS 2 MATH1502 3 MATH
NULL NULL CALCULUS 3 MATH1503 3 MATH
NULL NULL ALGEBRA MATH2333 3 MATH
NULL NULL DISCRETE MATHEMATICS MATH2410 3 MATH
MATH5501 MATH2333 Math Analysis MATH5501 3 MATH
NULL NULL AMERICAN CONSTITUTIO POLY1201 1 POLY
NULL NULL INTRO TO POLITICAL S POLY2001 3 POLY
POLY2103 POLY1201 AMERICAN GOVERNMENT POLY2103 2 POLY
NULL NULL SOCIALISM AND COMMUN POLY4103 4 POLY
POLY5501 POLY4103 POLITICS OF CUBA POLY5501 4 POLY
(33 row(s) affected)
 | 
