Outer Join

An outer join is a type of join that includes all of the rows from one of the tables joined, regardless of whether there were matches in the other table.

For example, assume you have two tables: Table A and Table B. Assume further that Table A does not contain matching rows for all of the values in Table B; however, you still want those unmatched rows to print in your report. In this example, you would apply an outer join to Table A. Your report would then print all the rows in Table A, filling in "null" for all rows not matched in Table B.

If you do not use an outer join, rows do not print unless finds a match between both tables.

Table 42. Table A

Dept #

Dept Name

Location

10

Accounting

New York

20

Research

Dallas

30

Sales

Chicago

40

Operation

Boston

Table 43. Table B

Emp #

Emp Name

Job

Dept #

7369

Smith

Clerk

20

7499

Allen

Salesman

30

7566

Jones

Manager

20

7782

Clark

Manager

10

Table 44. Without Outer Join

Dept Name

Emp #

Emp Name

Job

Dept #

Research

7369

Smith

Clerk

20

Sales

7499

Allen

Salesman

30

Research

7566

Jones

Manager

20

Accounting

7782

Clark

Manager

10

Table 45. With Outer Join

Dept Name

Emp #

Emp Name

Job

Dept #

Research

7369

Smith

Clerk

20

Sales

7499

Allen

Salesman

30

Research

7566

Jones

Manager

20

Accounting

7782

Clark

Manager

10

Operations

   

40