NOTE: All of these can be found on Wikipedia: Join (SQL).
There are three types of OUTER joins:
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
The keyword OUTER is optional in all implementations that follow the standard, so FULL JOIN is the same as FULL OUTER JOIN. (I’ve omitted the word OUTER from the SQL in the rest of this answer.)
Let’s look at what each does.
Consider the following two input data sets:
Set "A" Set "B"
AA BB
-------- --------
Item 1 Item 3
Item 2 Item 4
Item 3 Item 5
Item 4 Item 6
Notice that there are some items in A that aren’t in B, and vice versa.
Now, if we write an SQL statement like this, using LEFT join:
SELECT * FROM A LEFT JOIN B ON AA = BB
You’ll get the following result (the empty holes are actually NULL marks):
AA BB
-------- --------
Item 1
Item 2
Item 3 Item 3
Item 4 Item 4
Notice that you’ll get all the rows from AA, or rather, all the rows from the left part of the join clause.
If you switch to using a RIGHT join:
SELECT * FROM A RIGHT JOIN B ON AA = BB
AA BB
-------- --------
Item 3 Item 3
Item 4 Item 4
Item 5
Item 6
Notice that you get all the rows from the right part of the join clause.
However, if you want all the rows of both, you’ll use a FULL join:
SELECT * FROM A FULL JOIN B ON AA = BB
AA BB
-------- --------
Item 1 <-----+
Item 2 |
Item 3 Item 3 |
Item 4 Item 4 |
Item 5 +--- empty holes are NULL's
Item 6 |
^ |
| |
+---------------------+
As suggested in a comment, let me complete the other different ways to join.
With INNER join:
SELECT * FROM A INNER JOIN B ON AA = BB
AA BB
-------- --------
Item 3 Item 3
Item 4 Item 4
With INNER join we only get the rows that actually match up, no holes because of joining.
A CROSS join produces a cartesian product, by matching up every row from the first set with every row from the second set:
SELECT * FROM A CROSS JOIN B
AA BB
-------- --------
Item 1 Item 3 ^
Item 1 Item 4 +--- first item from A, repeated for all items of B
Item 1 Item 5 |
Item 1 Item 6 v
Item 2 Item 3 ^
Item 2 Item 4 +--- second item from A, repeated for all items of B
Item 2 Item 5 |
Item 2 Item 6 v
Item 3 Item 3 ... and so on
Item 3 Item 4
Item 3 Item 5
Item 3 Item 6
Item 4 Item 3
Item 4 Item 4
Item 4 Item 5
Item 4 Item 6
Also note that we don’t specify which columns that match, since there is no matching done.
Finally, NATURAL join, in this syntax we don’t specify which columns that match, but matches on column names. In our contrived example, no column names are the same, but let’s say for this specific example that the column names was XX in both tables, then we would get the following result:
SELECT * FROM A NATURAL JOIN B
+----------+------- matches on the names, and then the data
| |
v v
XX XX
-------- --------
Item 3 Item 3
Item 4 Item 4
As you can see, you get the same as a INNER join, but don’t have to type out the match part of the join clause.