LEFT JOIN SQL
A LEFT JOIN (one of several OUTER JOINs) is a meshing or combining of information between two tables. All of the rows of the first (or left) table are included in the results with additions to those rows from related unique rows on the second (or right) table. This is generally done by specifying id numbers in column one that are unique in column two. The syntax involved looks like this:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column_with_related_id = table2.column_with_unique_id
Most often the column_with_related_id is a FOREIGN KEY, while the column_with_unique_id in table2 is generally a PRIMARY KEY. This process makes table2 act as a virtual extension of table1. In the following table we have the transaction log of a game of Monopoly:
| 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 |
In our list of property we have the following:
| 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 |
We want to do an "OUTER" LEFT JOIN so that we know the names of the properties that were purchased in the first five transactions of our game.
SELECT transactions.trans_id, property.name
FROM transactions
LEFT JOIN property
ON transactions.prop_id = property.prop_id
ORDER BY transactions.trans_id
The result of this would be:
| trans_id | name |
|---|---|
| 1 | Connecticut Ave |
| 2 | |
| 3 | Vermont Ave |
| 4 | Mediterranean Ave |
| 5 | Baltic Ave |
Note that row 2 does not have a name. This is because there was no corresponding row number. A LEFT JOIN will list all of the rows on the left with corresponding info from the right. If there is no data, corresponding cells will be left blank.
< INNER JOIN SQL | RIGHT OUTER JOIN >
