Joining Tables

Joins are a fundamental aspect of relational databases. They allow you to extract data on entities related by design—represented by related tables—in your database.

A join occurs when you retrieve data from tables related by some shared data, often (but not necessarily) a shared reference number or index. For example, if you have a table of customers and a table of orders, good design in a relational database will ensure that the two are related together by some common data between the tables, perhaps by storing a customer number on every order record. By performing a join, you can retrieve information in a resultset where each row combines data from both tables: information about each order will be combined with information about the customer who placed it, and the combination will appear as such in the resultset.

As you will see in a moment, there are several kinds of joins. They work by considering a relationship between each row of one table and each row of another table; they differ in the way they allow relationships to be made and therefore in the way they relate tables together.

The Inner Join

The most common type of join is an inner join. When you perform an inner join between tables, rows with matching values are put into the resultset, but where values do not match (or a row simply isn't present in one table), they are ignored. Because the join looks for equivalence between the columns it specifies and is considered the default type of join, it is sometimes called an equi-join or simply a join.

As you'll see in a moment, there are two kinds of syntax for performing an inner join: a newer method that is ANSI-92 compliant, and an older method that isn't.

The modern ANSI syntax uses the INNER JOIN keywords in the FROM part of a SELECT statement, and this defines which tables will be joined and the relationship between them. The older syntax lists the tables to be joined in the FROM part, but separated by commas, and defines the relationship in the WHERE clause.

You can write a join using INNER JOIN like this:

SELECT *
FROM table1
INNER JOIN table2 ON table1.id_column = table2.id_column
						

or using the older form:

SELECT *
FROM table1, table2
WHERE table1.id_column = table2.id_column
						

Let's think of a content management system in which we have a table of articles, which holds items of news or features that may be published on the Web site. Here's a SELECT statement run through the mysql console that displays the contents of the table:

mysql> SELECT article_id, headline, user_id FROM articles;
+------------+------------------------------+---------+
| article_id | headline                     | user_id |
+------------+------------------------------+---------+
|          1 | MySQL to Adopt a Dolphin     |       2 |
|          2 | MySQL 4.1 goes live          |       3 |
|          3 | New Downloads on our Website |       2 |
+------------+------------------------------+---------+

There's also a table of users (authorized personnel), who may be writers of the articles, or editors who approve them:

mysql> SELECT user_id, name, auth_group FROM users;
+---------+----------------+------------+
| user_id | name           | auth_group |
+---------+----------------+------------+
|       2 | Clare Scriven  | writer     |
|       1 | Tony Butcher   | writer     |
|       3 | John Schreiber | writer     |
|       5 | Peter Stamp    | editor     |
+---------+----------------+------------+

We're going to perform an inner join between the tables to see who's written which articles.

We will write the join specifying the names of the two tables, users and articles, in the FROM clause, aliasing them for readability (using AS) to u and a, respectively, and relating the tables on the user_id column, which exists in both tables:

mysql> SELECT u.name, u.auth_group, a.headline
    -> FROM users AS u
    -> INNER JOIN articles AS a ON u.user_id = a.user_id;
+----------------+------------+------------------------------+
| name           | auth_group | headline                     |
+----------------+------------+------------------------------+
| Clare Scriven  | writer     | MySQL to Adopt a Dolphin     |
| John Schreiber | writer     | MySQL 4.1 goes live          |
| Clare Scriven  | writer     | New Downloads on our Website |
+----------------+------------+------------------------------+

See what happened? We matched rows where the user_id on the articles table corresponds with the user_id in the users table. If it's not immediately clear, take a moment to look back at the contents of the two preceding tables and perform the join in your head!

In this example, the user_id column has the same name in both tables. When this is the case, you can use the keyword USING rather than ON to simplify your SQL. The keyword USING should be followed in parentheses by the column or columns, separated by commas, which are to be matched across the tables. You could thus write the same query like this:

mysql> SELECT u.name, u.auth_group, a.headline
    -> FROM users AS u
    -> INNER JOIN articles AS a USING (user_id);
+----------------+------------+------------------------------+
| name           | auth_group | headline                     |
+----------------+------------+------------------------------+
| Clare Scriven  | writer     | MySQL to Adopt a Dolphin     |
| John Schreiber | writer     | MySQL 4.1 goes live          |
| Clare Scriven  | writer     | New Downloads on our Website |
+----------------+------------+------------------------------+

We could write the same query using the older syntax:

mysql> SELECT u.name, u.auth_group, a.headline
    -> FROM users AS u, articles AS a
    -> WHERE u.user_id = a.user_id;
+----------------+------------+------------------------------+
| name           | auth_group | headline                     |
+----------------+------------+------------------------------+
| Clare Scriven  | writer     | MySQL to Adopt a Dolphin     |
| John Schreiber | writer     | MySQL 4.1 goes live          |
| Clare Scriven  | writer     | New Downloads on our Website |
+----------------+------------+------------------------------+

You can add extra conditions to the WHERE clause; for example, you may want to ensure that you only list people who have their auth_group set to writer:

mysql> SELECT u.name, a.headline
    -> FROM users AS u
    -> INNER JOIN articles AS a ON u.user_id = a.user_id
							-> WHERE u.auth_group = 'writer';
						

or with the older syntax:

mysql> SELECT u.name, a.headline
    -> FROM users AS u, articles AS a
    -> WHERE u.user_id = a.user_id
    -> AND u.auth_group = 'writer';
						

Both output the following:

+----------------+------------------------------+
| name           | headline                     |
+----------------+------------------------------+
| Clare Scriven  | MySQL to Adopt a Dolphin     |
| John Schreiber | MySQL 4.1 goes live          |
| Clare Scriven  | New Downloads on our Website |
+----------------+------------------------------+

In the preceding examples, you may have noticed that we wrote things like this:

SELECT u.name, u.auth_group, a.headline...

See how we put the alias of each table name before the name of the field? You would be correct to assume that a.headline, for example, could also be written as just headline because the column headline only appears in the articles table, so the a. is redundant. Although this would be perfectly correct and a little more compact, I prefer to be totally clear in my SQL as to which column comes from which table, and it reduces the risk of my SQL containing an ambiguous column reference.

The Left Join

A left join returns all rows from the table referred to in the left-hand side of a JOIN, regardless of whether they match rows in the other table.

For example, we might want to list the people in the users table together with the articles they have written. The difference from an inner join is that the people will appear even if they haven't written anything. Where no match occurs, a NULL appears in the resultset rather than matching data.

The syntax is almost the same as the ANSI inner join, but with the words LEFT JOIN instead:

mysql> SELECT u.name, u.auth_group, a.headline
    -> FROM users AS u
    -> LEFT JOIN articles AS a ON u.user_id = a.user_id;
+----------------+------------+------------------------------+
| name           | auth_group | headline                     |
+----------------+------------+------------------------------+
| Clare Scriven  | writer     | MySQL to Adopt a Dolphin     |
| Clare Scriven  | writer     | New Downloads on our Website |
| Tony Butcher   | writer     | NULL                         |
| John Schreiber | writer     | MySQL 4.1 goes live          |
| Peter Stamp    | editor     | NULL                         |
+----------------+------------+------------------------------+

In this query, the NULLs under headline correspond with any user who has no articles associated with him.

A left join can also be useful if you want to write a query to identify rows in a table that have no corresponding entry in another table.

For example, let's say that we want to find all users who have failed to contribute an article. We just need to perform a left join and add a WHERE clause to look for NULL in the resultset:

mysql> SELECT u.name, u.auth_group
    -> FROM users AS u
    -> LEFT JOIN articles AS a ON u.user_id = a.user_id
    -> WHERE a.headline IS NULL;
+--------------+------------+
| name         | auth_group |
+--------------+------------+
| Tony Butcher | writer     |
| Peter Stamp  | editor     |
+--------------+------------+

A LEFT JOIN can also be used with the alternative syntax, LEFT OUTER JOIN, which is synonymous.

The Natural Joins

The natural join and natural left join are really just shortcuts to what you could achieve using an inner join and a left join, respectively. The shortcut is possible where you have columns in your tables with matching names.

In the preceding examples, we had a column called user_id in both the users and the articles table. Because the column has the same name in each, for our natural join we could write the following:

mysql> SELECT u.name, u.auth_group, a.headline
    -> FROM users AS u
    -> NATURAL JOIN articles AS a;
+----------------+------------+------------------------------+
| name           | auth_group | headline                     |
+----------------+------------+------------------------------+
| Clare Scriven  | writer     | MySQL to Adopt a Dolphin     |
| John Schreiber | writer     | MySQL 4.1 goes live          |
| Clare Scriven  | writer     | New Downloads on our Website |
+----------------+------------+------------------------------+

Note that a natural join as performed in the preceding example (really a simplified way of writing an inner join) omits the keyword INNER, and you should just write NATURAL JOIN.

For the natural left join, we would do this:

mysql> SELECT u.name, u.auth_group, a.headline
    -> FROM users AS u
    -> NATURAL LEFT JOIN articles AS a;
+----------------+------------+------------------------------+
| name           | auth_group | headline                     |
+----------------+------------+------------------------------+
| Clare Scriven  | writer     | MySQL to Adopt a Dolphin     |
| Clare Scriven  | writer     | New Downloads on our Website |
| Tony Butcher   | writer     | NULL                         |
| John Schreiber | writer     | MySQL 4.1 goes live          |
| Peter Stamp    | editor     | NULL                         |
+----------------+------------+------------------------------+

As you can see, a natural join or natural left join does the same things as an inner or left join, just using less SQL. However, you may find that for the tiny savings in code the meaning becomes less clear. Indeed, a natural join relates all columns that have matching names, and this may be more than you want! The natural join is used less commonly than the other kinds of joins.

The Cross Join

Users often inadvertently use a cross join when trying to construct an inner join in the non-ANSI method, the telltale sign being a huge resultset. A cross join is also known as a Cartesian join or a full join.

A cross join is performed like this:

SELECT *
FROM table1, table2
						

As you can see, there is neither a JOIN keyword nor is there any relationship being defined between the tables in a WHERE clause.

Every row of each table is crossed with every row of every other table, even if there's nothing to match between them. This results in many combinations. For example:

mysql> SELECT u.name, u.auth_group, a.headline
    -> FROM users AS u, articles AS a;
+----------------+------------+------------------------------+
| name           | auth_group | headline                     |
+----------------+------------+------------------------------+
| Clare Scriven  | writer     | MySQL to Adopt a Dolphin     |
| Tony Butcher   | writer     | MySQL to Adopt a Dolphin     |
| John Schreiber | writer     | MySQL to Adopt a Dolphin     |
| Peter Stamp    | editor     | MySQL to Adopt a Dolphin     |
| Clare Scriven  | writer     | MySQL 4.1 goes live          |
| Tony Butcher   | writer     | MySQL 4.1 goes live          |
| John Schreiber | writer     | MySQL 4.1 goes live          |
| Peter Stamp    | editor     | MySQL 4.1 goes live          |
| Clare Scriven  | writer     | New Downloads on our Website |
| Tony Butcher   | writer     | New Downloads on our Website |
| John Schreiber | writer     | New Downloads on our Website |
| Peter Stamp    | editor     | New Downloads on our Website |
+----------------+------------+------------------------------+

As you can see, the query appears out of control! The cross join is not commonly used, at least not intentionally.