FULL JOIN
A FULL JOIN is an OUTER JOIN. It is like doing both the LEFT JOIN and the RIGHT JOIN simultaneously. In the SELECT statement the FULL JOIN specifies the combination of data from two separate tables based on a comparison of the data from two specific columns within them. The comparison is usually done between a PRIMARY KEY in one table and a related FOREIGN KEY in the other. All of the rows of both table are the result, with the rows combined where the KEY comparison matches. However, if there are no matches, NO rows will be returned. The syntax of the FULL JOIN follows:
SELECT table1.column_desired, table2.column_also_needed, etc.
FROM table1
FULL JOIN table2
ON table1.column = table2 column
In a Monopoly game we have the following table listing properties on the game board:
| prop_id | name | price | group |
|---|---|---|---|
| 1 | Baltic Ave | 60 | purple |
| 2 | Mediterranean Ave | 60 | purple |
| 3 | Reading Railroad | 200 | rr |
| 4 | Oriental Ave | 100 | light blue |
| 5 | Vermont Ave | 100 | light blue |
| 6 | Connecticut Ave | 120 | light blue |
On a log, called transactions, we keep track of initial property purchases:
| trans_id | owner_name | prop_id | turn |
|---|---|---|---|
| 1 | Mary | 6 | 1 |
| 2 | Mary | 9 | 6 |
| 3 | Tom | 5 | 8 |
| 4 | Joe | 2 | 14 |
| 5 | Joe | 1 | 37 |
We wish to do a full join on these tables that will give us all the property names and their owners, but also let us know if the players own property not on our admittedly limited list.
SELECT properties.name, transactions.owner_name
FROM properties
FULL JOIN transactions
ON properties.prop_id = transactions.prop_id
ORDER BY name
| name | owner_name |
|---|---|
| Baltic Ave | Joe |
| Mediterranean Ave | Joe |
| Reading Railroad | |
| Oriental Ave | |
| Vermont Ave | Tom |
| Connecticut Ave | Mary |
| Mary |
Note that all of the rows are represented and that anywhere there is no corresponding information between the rows, the appropriate cell is left blank. We now know that Joe owns the purple monopoly, Tom owns Vermont Ave, Mary owns Connecticut Ave plus an un-named property not on our list.
< RIGHT OUTER JOIN | UNION SQL >
