Inner and Outer Joins Explained
Inner Joins
An "Iner Join" connects only those rows in either table
that
have matching keys in the other table.
So, if some rows in
either table do not have a matching key in BOTH tables
those rows do not appear in the resulting view.
How do you find
the rows that are not in the “Inner Join” results ?
Outer Joins
An "Outer
Join" is used to show
all
of the rows in one table
and …
those
that do have matching keys in the other table.
Whether you use a
"Left" or a "Right" Outer join depends on
which table you want to see all of the rows.
First, remember
that the "typical" relationship with which we're dealing
is a "one to many" relationship.
In a "one to
many" relationship, the "one" side has the primary key
(or candidate key, if the actual primary key has not been assigned yet)
and the "many" side has the foreign key.
With that in mind,
the "left" table is the one with the primary (or candidate) key
and
the
"right" table is the one with the foreign key in it.
So,
a
"Left Outer Join" shows all the rows in the "Left" (primary
key) table
and
a
"Right Outer Join" shows all the rows in the "Right"
(foreign key) table.
What’s the purpose behind these ?
Left Outer
Join :
Yields results that show primary keys that have no matching foreign
keys.
Example
:
Customers who have no orders, which is not really a problem
usually.
Right Outer Join :
Orders
that have no customers are more of a problem.
Yields results that show foreign keys that have no matching
primary keys.
Example
:
This
would help to show orders that have no customers,
which usually is a real problem : who pays for the
order ?
To summarize
:
All
of the joins show all rows that match in both tables,
but
the outer joins ALSO show all of the rows in the
one of the tables,
regardless of whether or not they match in the other table.
Sometimes this is very useful.