Example B1: This example illustrates the results of the various joins using tables J1 and J2. These tables contain rows as shown.
SELECT * FROM J1 W X --- ------ A 11 B 12 C 13 SELECT * FROM J2 Y Z --- ------ A 21 C 22 D 23
The following query does an inner join of J1 and J2 matching the first column of both tables.
SELECT * FROM J1 INNER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 C 13 C 22
In this inner join example the row with column W='C' from J1 and the row with column Y='D' from J2 are not included in the result because they do not have a match in the other table. Note that the following alternative form of an inner join query produces the same result.
SELECT * FROM J1, J2 WHERE W=Y
The following left outer join will get back the missing row from J1 with nulls for the columns of J2. Every row from J1 is included.
SELECT * FROM J1 LEFT OUTER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 B 12 - - C 13 C 22
The following right outer join will get back the missing row from J2 with nulls for the columns of J1. Every row from J2 is included.
SELECT * FROM J1 RIGHT OUTER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 C 13 C 22 - - D 23
The following full outer join will get back the missing rows from both J1 and J2 with nulls where appropriate. Every row from both J1 and J2 is included.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 C 13 C 22 - - D 23 B 12 - -
Example B2: Using the tables J1 and J2 from the previous example, examine what happens when and additional predicate is added to the search condition.
SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=13 W X Y Z --- ------ --- ------ C 13 C 22
The additional condition caused the inner join to select only 1 row compared to the inner join in Example B1.
Notice what the impact of this is on the full outer join.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=13 W X Y Z --- ------ --- ------ - - A 21 C 13 C 22 - - D 23 A 11 - - B 12 - -
The result now has 5 rows (compared to 4 without the additional predicate) since there was only 1 row in the inner join and all rows of both tables must be returned.
The following query illustrates that placing the same additional predicate in WHERE clause has completely different results.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y
WHERE X=13
W X Y Z
--- ------ --- ------
C 13 C 22
The WHERE clause is applied after the intermediate result of the full outer join. This intermediate result would be the same as the result of the full outer join query in Example B1. The WHERE clause is applied to this intermediate result and eliminates all but the row that has X=13. Choosing the location of a predicate when performing outer joins can have significant impact on the results. Consider what happens if the predicate was X=12 instead of X=13. The following inner join returns no rows.
SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=12
Hence, the full outer join would return 6 rows, 3 from J1 with nulls for the columns of J2 and 3 from J2 with nulls for the columns of J1.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=12 W X Y Z --- ------ --- ------ - - A 21 - - C 22 - - D 23 A 11 - - B 12 - - C 13 - -
If the additional predicate is in the WHERE clause instead, 1 row is returned.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y
WHERE X=12
W X Y Z
--- ------ --- ------
B 12 - -
Example B3: List every department with the employee number and last name of the manager, including departments without a manager.
SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
ON MGRNO = EMPNO
Example B4: List every employee number and last name with the employee number and last name of their manager, including employees without a manager.
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E LEFT OUTER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO
The inner join determines the last name for any manager identified in the DEPARTMENT table and the left outer join guarantees that each employee is listed even if a corresponding department is not found in DEPARTMENT.