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_idowner_nameprop_idturn
1Mary61
2Mary96
3Tom58
4Joe214
5Joe137

In our list of property we have the following:

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


Send notes in disappearing ink!

Interesting Pages