MySQL – JOIN Syntax (LEFT, RIGHT, INNER and OUTER JOIN)

June 6, 2007 10 Comments

JOIN Syntax

MySQL supports the following JOIN syntaxes for the table_references part of SELECT statements and multiple-table DELETE and UPDATE statements:

table_references:
table_reference, table_reference
| table_reference [INNER | CROSS] JOIN table_reference [join_condition]
| table_reference STRAIGHT_JOIN table_reference
| table_reference LEFT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
| table_reference RIGHT [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference:
tbl_name [[AS] alias] [index_hint)]

join_condition:
ON conditional_expr
| USING (column_list)

index_hint:
USE {INDEX|KEY} (index_list)]
| IGNORE {INDEX|KEY} (index_list)]
| FORCE {INDEX|KEY} (index_list)]

index_list:
index_name [, index_name] …

Index hints can be specified to affect how the MySQL optimizer makes use of indexes. For more information, see Section 13.2.7.2, “Index Hint Syntax”.

Note that several changes in join processing were made in MySQL 5.0.12 to make MySQL more compliant with standard SQL. These changes include the ability to handle nested joins (including outer joins) according to the standard. If a nested join returns results that are not what you expect, please consider upgrading to MySQL 5.0. Further details about the changes in join processing can be found at http://dev.mysql.com/doc/refman/5.0/en/join.html.

You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause. There are exceptions to this rule.

Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.

The { OJ … LEFT OUTER JOIN …} syntax shown in the preceding list exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

MySQL – LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN
In a database such as MySQL, data is divided into a series of tables (the “why” is beyond what I’m writing today) which are then connected together in SELECT commands to generate the output required. I find when I’m running MySQL training, people often get confused between all the join flavours. Let me give you an example to see how it works.

If this isn’t quite the question you’re looking to have answered, we’ve got a MySQL IAQ (Infrequently Answered Questions that may help you

First, some sample data:
Mr Brown, Person number 1, has a phone number 01225 708225
Miss Smith, Person number 2, has a phone number 01225 899360
Mr Pullen, Person number 3, has a phone number 01380 724040
and also:
Person number 1 is selling property number 1 – Old House Farm
Person number 3 is selling property number 2 – The Willows
Person number 3 is (also) selling property number 3 – Tall Trees
Person number 3 is (also) selling property number 4 – The Melksham Florist
Person number 4 is selling property number 5 – Dun Roamin.

mysql> select * from demo_people;

+————+————–+——+

| name | phone | pid |

+————+————–+——+

| Mr Brown | 01225 708225 | 1 |

| Miss Smith | 01225 899360 | 2 |

| Mr Pullen | 01380 724040 | 3 |

+————+————–+——+

3 rows in set (0.00 sec)

mysql> select * from demo_property;

+——+——+———————-+

| pid | spid | selling |

+——+——+———————-+

| 1 | 1 | Old House Farm |

| 3 | 2 | The Willows |

| 3 | 3 | Tall Trees |

| 3 | 4 | The Melksham Florist |

| 4 | 5 | Dun Roamin |

+——+——+———————-+

5 rows in set (0.00 sec)

mysql>

If I do a regular JOIN (with none of the keywords INNER, OUTER, LEFT or RIGHT), then I get all records that match in the appropriate way in the two tables, and records in both incoming tables that do not match are not reported:

mysql> select name, phone, selling

from demo_people join demo_property

on demo_people.pid = demo_property.pid;

+———–+————–+———————-+

| name | phone | selling |

+———–+————–+———————-+

| Mr Brown | 01225 708225 | Old House Farm |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

+———–+————–+———————-+

4 rows in set (0.01 sec)

mysql>

If I do a LEFT JOIN, I get all records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join – thus ensuring (in my example) that every PERSON gets a mention:

mysql> select name, phone, selling

from demo_people left join demo_property

on demo_people.pid = demo_property.pid;

+————+————–+———————-+

| name | phone | selling |

+————+————–+———————-+

| Mr Brown | 01225 708225 | Old House Farm |

| Miss Smith | 01225 899360 | NULL |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

+————+————–+———————-+

5 rows in set (0.00 sec)

mysql>

If I do a RIGHT JOIN, I get all the records that match and IN ADDITION I get an extra record for each unmatched record in the right table of the join – im my example, that means that each property gets a mention even if we don’t have seller details:

mysql> select name, phone, selling

from demo_people right join demo_property

on demo_people.pid = demo_property.pid;

+———–+————–+———————-+

| name | phone | selling |

+———–+————–+———————-+

| Mr Brown | 01225 708225 | Old House Farm |

| Mr Pullen | 01380 724040 | The Willows |

| Mr Pullen | 01380 724040 | Tall Trees |

| Mr Pullen | 01380 724040 | The Melksham Florist |

| NULL | NULL | Dun Roamin |

+———–+————–+———————-+

5 rows in set (0.00 sec)

mysql>

An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples – it’s provided for ODBC compatability and doesn’t add an extra capabilities.

Information, Software, Technology