Joins

We are going to look at the three types of join that you will need to use at some time or another

• INNER JOIN
• LEFT JOIN
• cartesian join

You can just use "JOIN" instead of "INNER JOIN" as, by default, the INNER is implicit. Similarly, "LEFT JOIN" is actually "LEFT OUTER JOIN", but again, the "OUTER" is implicit.

At the end of this tutorial we will look at an exercise involving all three.

INNER JOIN

This is the most commonly used of the three and is use to get data from matching rows in different tables.
Let's look at an example from our database.

Problem: List pupils in Jardine house showing house name and pupil name.

Input

Our table data. Each pupil has their houseID as part of the data and each house has a houseID. As indicated in our DB schema link, this will be the basis of our "join condition". We want rows from the pupil table where its houseID matches the id from the house table.

house                                                    pupil (extracted sample)            
+---------+------------+                                 +---------+-----------------+
| houseID | house_name |                                 | houseID | pupil_name      |
+---------+------------+                                 +---------+-----------------+
|       1 | Grace      |                                 |    ...  |  ...            |
|       2 | Laker      |                                 |       4 | Mary Sheldon    |
|       3 | Cowdrey    |                                 |       3 | Mary Blake      |
|       4 | Jardine    |                                 |       4 | Michael Grove   |
+---------+------------+                                 |       1 | Mary Whitehouse |
                                                         |       3 | Margaret Norton |
                                                         |    ...  |  ...            |
                                                         +---------+-----------------+

Query

A note on style and syntax. I could have wrtten this query as

1. A common convention is upper case keywords and lower case table and column names
2. Split the query over several lines. Not only is it more readable but when mysql_error() reports an error it tells you on which line.
3. Don't use "... FROM A,B WHERE ...". Keep the join condition separate from the selection criteria with "...INNER JOIN ... ON"
4. Use aliases for tables. Again for legibility but also the table name only occurs once. If you mis-spell or want to change it, it's easier than if it is repeated with every column. (Especially if your tablename is something like "residual_hypoglycaemic_oxidization_factors")

Results

There are six pupils with houseID=4 so six rows are returned, each with a pupil name and the matching house name

+------------+----------------+
| house_name | pupil_name     |
+------------+----------------+
| Jardine    | Anne Bailey    |
| Jardine    | John Patterson |
| Jardine    | John Tully     |
| Jardine    | Mary Sheldon   |
| Jardine    | Michael Grove  |
| Jardine    | Peter Appleby  |
+------------+----------------+

Problem : Which pupils chose Maths?

From our schema, to link from the subject table to the pupil table we need to link via the choice table. So we join from pupil to choice using the pupilID column and from subject to choice using the subjectID column. This time we'll use a slightly different syntax. Because the column names we are joining on are the same in both tables, we can can use the USING keyword

Results:

+-----------------+
| pupil_name      |
+-----------------+
| William Smith   |
| Henry Irving    |
| David Powell    |
| Wayne Jones     |
| John Tully      |
| Mary Whitehouse |
| Peter Appleby   |
+-----------------+

Problem : How many pupils chose each subject?

Our subject table contains the following

+-----------+-----------+
| subjectID | subject   |
+-----------+-----------+
|         1 | English   |
|         2 | German    |
|         3 | Geography |
|         4 | Maths     |
|         5 | Economics |
|         6 | Biology   |
|         7 | Chemistry |
|         8 | Physics   |
|         9 | History   |
|        10 | Computing |
+-----------+-----------+

We need to count the number of records in choice that match each subject

Query:

Results:

+-----------+-----------+-------+
| subjectID | subject   | total |
+-----------+-----------+-------+
|         1 | English   |    12 |
|         2 | German    |    11 |
|         3 | Geography |     6 |
|         4 | Maths     |     7 |
|         5 | Economics |    13 |
|         6 | Biology   |     6 |
|         7 | Chemistry |     6 |
|         8 | Physics   |    10 |
|         9 | History   |    10 |
+-----------+-----------+-------+

There are ten subjects but this query gives only nine totals. Earlier I said subjects could be chosen by none, one or many pupils and the way we query the table would be affected. The INNER JOIN only totals matching records, so what if there is no match? How do we see that "computing" was chosen by no pupils?

Enter the LEFT JOIN.