SQL Joins

These are my personal reference notes. This not a tutorial or anything like that. All of these examples are taken from SQL Pocket Guide, Second Edition. It's a good pocket reference. You should buy it.

A Simple Join

mysql> SELECT u.name AS fall, c.name AS county
    -> FROM upfall u, county c
    -> WHERE u.county_id = c.id;
+-----------------+-----------+
| fall            | county    |
+-----------------+-----------+
| Munising Falls  | Alger     | 
| Tannery Falls   | Alger     | 
| Alger Falls     | Alger     | 
| Wagner Falls    | Alger     | 
| Horseshoe Falls | Alger     | 
| Miners Falls    | Alger     | 
| Little Miners   | Alger     | 
| Scott Falls     | Alger     | 
| Twin Falls #1   | Alger     | 
| Twin Falls #2   | Alger     | 
| Canyon Falls    | Baraga    | 
| Agate Falls     | Ontonogan | 
| Bond Falls      | Ontonogan | 
| Fumee Falls     | Dickenson | 
| Kakibika Falls  | Gogebic   | 
| Rapid River Fls | Delta     | 
+-----------------+-----------+
16 rows in set (0.00 sec)

Cross Joins

Cross joins produce Cartesian products:

SELECT * 
FROM upfall CROSS JOIN county;

is equivalent to

SELECT * FROM upfall, county;

Inner Joins

Joins rows from multiple tables if they meet the ON condition.

mysql> SELECT u.name AS fall, c.name as county
    -> FROM county c INNER JOIN upfall u
    -> ON u.county_id = c.id
    -> WHERE c.population > 1000;
+-----------------+-----------+
| fall            | county    |
+-----------------+-----------+
| Munising Falls  | Alger     | 
| Tannery Falls   | Alger     | 
| Alger Falls     | Alger     | 
| Wagner Falls    | Alger     | 
| Horseshoe Falls | Alger     | 
| Miners Falls    | Alger     | 
| Little Miners   | Alger     | 
| Scott Falls     | Alger     | 
| Twin Falls #1   | Alger     | 
| Twin Falls #2   | Alger     | 
| Canyon Falls    | Baraga    | 
| Agate Falls     | Ontonogan | 
| Bond Falls      | Ontonogan | 
| Fumee Falls     | Dickenson | 
| Kakibika Falls  | Gogebic   | 
| Rapid River Fls | Delta     | 
+-----------------+-----------+
16 rows in set (0.00 sec)

The USING Clause

When creating a join between tables based on identically named columns with identical values (equi-joins), the ON clause can be replaced with the USING clause. Note that the "INNER" can be omitted from "INNER JOIN"

mysql> SELECT *
    -> FROM fall_description
    -> JOIN fall_location USING (id);

Natural Join

If tables are to be joined along all matching columns of the same name, you can use the NATURAL JOIN shortcut.

SELECT id
FROM fall_description fd
NATURAL JOIN fall_location fl

Outer Joins to Follow