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_idnamepricegroup
1Baltic Ave60purple
2Mediterranean Ave60purple
3Reading Railroad200rr
4Oriental Ave100light blue
5Vermont Ave100light blue
6Connecticut Ave120light blue

On a log, called transactions, we keep track of initial property purchases:

trans_idowner_nameprop_idturn
1Mary61
2Mary96
3Tom58
4Joe214
5Joe137

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
nameowner_name
Baltic AveJoe
Mediterranean AveJoe
Reading Railroad
Oriental Ave
Vermont AveTom
Connecticut AveMary
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 >


Send notes in disappearing ink!

Interesting Pages