<? echo

Project Statuses

Mental Fusion - 85%
FusionBB - 2%
This webpage is built with W3C compliant HTML5.
This webpage is built with W3C compliant CSS3.
Download: Fast, Fun, Awesome
Opera, the fastest and most secure web browser

Example Information

The MySQL JOIN Statement

Posted: February 14, 2012 11:32 AM CST by Michael
There are three major types of JOIN in MySQL. Those are: INNER, LEFT, and RIGHT.

Odds are, you've used INNER JOIN without even knowing it. INNER JOIN is synonymous with the MySQL comma operator (which is, you guessed it, a comma: ",").

So what do each of these JOIN statements do? Let's find out.

First we need a couple of tables of data, so let's create two tables: people and birthdays. We'll create them with very simple structures for the sake of this example:

mysql> CREATE TABLE people (id int primary key auto_increment, name varchar(30));
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE birthdays (user_id int, birthdate date);
Query OK, 0 rows affected (0.02 sec)

No we populate the tables with a bit of data:

mysql> INSERT INTO people (name) VALUES ('Bob'),('Ryan'),('Jessica');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> INSERT INTO birthdays (user_id, birthdate) VALUES (1,'1984-04-23'),(2,'1974-03-12'),(3,'1981-11-15');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

After that, our tables should look like this:

mysql> SELECT * FROM people;
+----+---------+
| id | name |
+----+---------+
| 1 | Bob |
| 2 | Ryan |
| 3 | Jessica |
+----+---------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM birthdays;
+---------+------------+
| user_id | birthdate |
+---------+------------+
| 1 | 1984-04-23 |
| 2 | 1974-03-12 |
| 3 | 1981-11-15 |
+---------+------------+
3 rows in set (0.00 sec)

Now that we've got our tables set up, let's see how each JOIN behaves when interactive with the tables.

First, we'll look at the INNER JOIN as it is the most basic and easy to understand. The syntax for using any JOIN command is as follows: SELECT [columns] FROM [table1] [(LEFT|RIGHT|INNER) JOIN] [table2] ON [table1.column] = [table2.column]

Let's see how a very basic INNER JOIN reacts to our two tables:

mysql> SELECT * FROM people INNER JOIN birthdays ON people.id = birthdays.user_id;
+----+---------+---------+------------+
| id | name | user_id | birthdate |
+----+---------+---------+------------+
| 1 | Bob | 1 | 1984-04-23 |
| 2 | Ryan | 2 | 1974-03-12 |
| 3 | Jessica | 3 | 1981-11-15 |
+----+---------+---------+------------+
3 rows in set (0.00 sec)

What happened? Each row in people was paired up with its matching row in birthdays. We can limit the number of fields returned by specifying a list of columns in the SELECT query; by doing so, we won't get the duplicated id field:

mysql> SELECT id, name, birthdate FROM people INNER JOIN birthdays ON people.id = birthdays.user_id;
+----+---------+------------+
| id | name | birthdate |
+----+---------+------------+
| 1 | Bob | 1984-04-23 |
| 2 | Ryan | 1974-03-12 |
| 3 | Jessica | 1981-11-15 |
+----+---------+------------+
3 rows in set (0.00 sec)

Now, it's important to note that in this example there are no conflicting column names. In the case where column names conflict, you MUST specify which table the column belongs to by adding the table's name or its alias to the column name (e.g., if the user_id column in the birthdays table was id instead, this column could conflict when selecting the id for use in the result set unless the SELECT statement selected "people.id" or "birthdays.id" instead).

INNER JOIN statements can be simplified by using the comma operator so that less typing is required for each query; however, it can be less clear than actually typing out INNER JOIN especially in the case of multiple JOIN statements or subqueries. When using the comma operator, we have to use a WHERE claus to specify which columns to match on instead of an ON statement:

mysql> SELECT id, name, birthdate FROM people, birthdays WHERE people.id = birthdays.user_id;
+----+---------+------------+
| id | name | birthdate |
+----+---------+------------+
| 1 | Bob | 1984-04-23 |
| 2 | Ryan | 1974-03-12 |
| 3 | Jessica | 1981-11-15 |
+----+---------+------------+
3 rows in set (0.00 sec)

Here's where things get tricky. What happens when there's data in one table that doesn't have a matching row in another table? Let's add a new person to our people table and a new birthdate to our birthdays table with differing IDs:

mysql> INSERT INTO people (name) VALUES ('Michael'); -- Shameless self plug
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO birthdays (user_id, birthdate) VALUES (5,'1901-06-27');
Query OK, 1 row affected (0.01 sec)

Now, when we try to select that data again using the INNER JOIN from before, what do you think we'll get?

mysql> SELECT id, name, birthdate FROM people INNER JOIN birthdays ON people.id = birthdays.user_id;
+----+---------+------------+
| id | name | birthdate |
+----+---------+------------+
| 1 | Bob | 1984-04-23 |
| 2 | Ryan | 1974-03-12 |
| 3 | Jessica | 1981-11-15 |
+----+---------+------------+
3 rows in set (0.00 sec)

The exact same result set as before. Why? The reson is that INNER JOIN returns only matching results for each table. If either table has data but no matching row in the other table, the information isn't returned. No worries, LEFT JOIN and RIGHT JOIN can help us with that problem.

LEFT JOIN and RIGHT JOIN are unique in that they will return data for both tables even if one of the tables doesn't have a matching row. They are largely the same in their function with the exception that LEFT JOIN evaluates the left table first and returns results, if any, based on that table and any associations in the second (or right) table. So what happens when we use LEFT JOIN on the same data set as before? Changing nothing but the word INNER in INNER JOIN to LEFT, we get the following:

mysql> SELECT id, name, birthdate FROM people LEFT JOIN birthdays ON people.id = birthdays.user_id;
+----+---------+------------+
| id | name | birthdate |
+----+---------+------------+
| 1 | Bob | 1984-04-23 |
| 2 | Ryan | 1974-03-12 |
| 3 | Jessica | 1981-11-15 |
| 4 | Michael | NULL |
+----+---------+------------+
4 rows in set (0.00 sec)

What's this? The 'Michael' entry we added to the people table showed! In this example, birthdate is NULL because the id 4 does not have an entry in the birthdays table. If we add in the missing entry and try our SELECT again, let's see what happens:

mysql> INSERT INTO birthdays (user_id, birthdate) VALUES (4,'1984-05-29');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT id, name, birthdate FROM people LEFT JOIN birthdays ON people.id = birthdays.user_id;
+----+---------+------------+
| id | name | birthdate |
+----+---------+------------+
| 1 | Bob | 1984-04-23 |
| 2 | Ryan | 1974-03-12 |
| 3 | Jessica | 1981-11-15 |
| 4 | Michael | 1984-05-29 |
+----+---------+------------+
4 rows in set (0.00 sec)

Wonderful! Now all our data is up to date! Right? RIGHT JOIN will help us evaluate the birthdays table without having to move arguments in the query around. As before, just change LEFT JOIN to RIGHT JOIN to test this one out; doing so will force MySQL to evaluate the right table first. In this example, the query will evaluate the birthdays table first and then attempt to find matches within the people table:

mysql> SELECT id, name, birthdate FROM people RIGHT JOIN birthdays ON people.id = birthdays.user_id;
+------+---------+------------+
| id | name | birthdate |
+------+---------+------------+
| 1 | Bob | 1984-04-23 |
| 2 | Ryan | 1974-03-12 |
| 3 | Jessica | 1981-11-15 |
| NULL | NULL | 1901-06-27 |
| 4 | Michael | 1984-05-29 |
+------+---------+------------+
5 rows in set (0.00 sec)

After using RIGHT JOIN, the birthdate that's missing its person is evident. We can either add the person, or remove the birthdate from the birthdays table to keep our data consistent. Let's remove it:

mysql> DELETE FROM birthdays WHERE birthdate = '1901-06-27';
Query OK, 1 row affected (0.00 sec)

Now, all of our JOIN statements will return the exact same set of data and all is right with the MySQL world.

For more information on the MySQL JOIN syntax, head over to MySQL's JOIN Syntax manual entry.
; ?>