Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Sep 30th, 2011

 

First to define what Inner Join and Outer Join mean.

Inner Join:
A join between two tables were only those records in both tables that meet the conditions of the join expression are returned. In most instances a inner join is a equi-join based on the Primary Key/Foreign Keys common to both tables, however this is not always the case, since additional conditions may be added to the join express to filter the records returned from both tables. While any relational comparison operator can be used ("=", "<", ">", "<=", ">=", or "<>") to join the tables, the only meaningful operators is that sub-set of operators ("=" and "<>") that can be expressed on the Primary Key/Foreign Keys common to both tables (i.e. .PK <= .PK would not make any sense in a RDBMS). The supplement of logic operators ("and" & "or") can be used to enhance the join condition with additional logical conditional expressions, and thus filter the records returned common to both tables.

Outer Join:
A join between two tables were there is no requirement that each record in the joined tables to have a match. Additionally, Outer Joins are subdivided into "Left Outer", "Right Outer", and "Full Outer".

Given two Tables: A and B

A LEFT OUTER JOIN B ON
- Returns all records from Table A, and those records from Table B that do not meet the NULL values are returned is used to represent the absent of those values missing from Table B.

A RIGHT OUTER JOIN B ON
- Returns all records from Table B, and those records from Table A that do not meet the NULL values are returned is used to represent the absent of those values missing from Table A.

A FULL OUTER JOIN B ON
- Returns all records from Table A and all the records from Table B, and for those records from Table B which do not meet the NULL values are returned in the Table B results and those records from Table A which do not meet the NULL values are returned in the Table A results.

As with the Inner Join, additional conditions may be added to the join express to filter the records returned from the resulting join.

So in summary;
Inner Joins return only those records in both tables that match the conditions of the join expression, where for Outer Joins there is no requirement that each record in the joined tables to have a match. Additionally, Outer Join is subdivided into three types of joins, "Left Outer", "Right Outer", and "Full Outer". Inner Join is not subdivided.