Someone will probably tell me that this is an elementary-level topic, but I got some good questions regarding joins from my most recent ZCE class students, so I thought I'd put down the examples that I used to explain this to them. Being able to join with confidence is a key skill, because it means that you can refactor and normalise your data, without worrying about how hard something will be to retrieve.

The database structure I'm using here looks like this (the same example that I used when I wrote the Databases chapter for PHP Master):

If you want to grab the actual data I'm using for my examples, then here's the zipped mysqldump file: …

Someone will probably tell me that this is an elementary-level topic, but I got some good questions regarding joins from my most recent ZCE class students, so I thought I’d put down the examples that I used to explain this to them. Being able to join with confidence is a key skill, because it means that you can refactor and normalise your data, without worrying about how hard something will be to retrieve.

The database structure I’m using here looks like this (the same example that I used when I wrote the Databases chapter for PHP Master):

If you want to grab the actual data I’m using for my examples, then here’s the zipped mysqldump file: …

If you used to write MySQL joins for MySQL versions < 5.0 or upgrade your server from MySQL 4 to MySQL >= 5.0 you maybe run into a problem when you execute the following query:

SELECT *
FROM mytable1, mytable2
INNER JOIN mytable3
ON mytable1.mycolumnname = mytable3.mycolumnname
WHERE mytable1.id = mytable2.id;

#1054 – Unknown column ‘mytable.mycolumnname’ in ‘on clause’

Even though you made sure that the column exists, the problem persists. It can be a very annoying and time-consuming task to track this kind of error down to it’s cause: MySQL starting from version 5.0 tries to be more compliant to ANSI SQL. The tables are beeing joined in a different order. The solution to this problem is actually very simple. Surround the tables in the FROM clause with round brackets:

SELECT *
FROM (mytable1, …

Here you find information about writing inner joins with the comma operator. It’s the most basic way to combine (join) two tables. There is an alternative syntax that can be used, because in MySQL you can write inner joins in two different ways. Another popular way is it to use the INNER JOIN command or synonymous keywords like CROSS JOIN and JOIN. Please make sure to read our dedicated documentation for more information when you understand the comma operator syntax.

Syntax

The following examples are equivalent to the INNER JOIN …

This tutorial explains how you can use NATURAL JOINs and what a natural join is actually. Included are syntax details and example statements.

Generally speaking, the keyword NATURAL can be treated as a join condition which is added implicitly. If used, it replaces the keywords ON and USING altogether. In MySQL writing natural joins means adding the keyword NATURAL to either an INNER JOIN or an OUTER JOIN. Let’s take a look at how a natural join implies a join condition.

Syntax

First of all, some natural join syntax examples. As mentioned earlier, a natural join adds a implicit condition to inner and outer join statements:

-- natural inner …

Here you find information about writing RIGHT JOINs (also referred to as RIGHT OUTER JOINs). This introduction into right joins includes a detailed description, syntax information and right outer join example statements. The Venn diagram on the left represents a result set that a statement with a right join produces. Please refer to the syntax examples below for an example. Links to additional information resources can be found at the end of this article.

Right Join syntax

First of all, some syntax examples for the impatient:

-- right join with USING-clause
SELECT *
FROM <leftTable> RIGHT JOIN <rightTable>
USING(id)

-- right join with ON-clause
SELECT *
FROM <leftTable> a RIGHT JOIN <rightTable> b …

Here you find information about writing LEFT JOINs (also referred to as LEFT OUTER JOINs). This introduction into left joins includes a description, syntax information and example statements that use left outer joins. The Venn diagram on the left represents a result set that a statement with a left join produces. Please refer to the syntax examples below for an example. Links to additional information resources can be found at the end of this article.

Left Join syntax

First of all, some syntax examples for the impatient:

-- left join with USING-clause
SELECT *
FROM <leftTable> LEFT JOIN <rightTable>
USING(id)

-- left join with ON-clause
SELECT *
FROM <leftTable> a LEFT JOIN <rightTable> b
ON a.name = b.authorName

As you can see, a join condition can be written …

Here you find information about OUTER JOINs in MySQL statements. This tutorial is a general introduction. Syntax details and example statements are split into a LEFT OUTER JOIN tutorial and RIGHT OUTER JOIN tutorial because these are specific outer joins. 

Outer Join basics

MySQL supports outer joins. They identify matching rows that are stored in two different tables and add the selected columns to the result set. That’s exactly what an inner join does as well.

The difference between inner and outer join is: An outer join can identify rows without a match in the joined table. When …

This tutorial shows you how to write ANSI-Style inner joins with the INNER JOIN keywords. Included are a general description, some syntax examples and a comparison between inner and cross joins.

Note: In MySQL the join keywords JOIN and CROSS JOIN are synonymous with INNER JOIN. That means: All example statements found in this article work fine when you replace INNER JOIN with JOIN or CROSS JOIN.

Syntax

Here are syntax examples for the impatient. Basically, ANSI-style join conditions can be specified with two different keywords: USING and ON. Take a look at the following examples:

-- inner join with USING clause
SELECT *
FROM <firstTable> a INNER JOIN <anotherTable> b
USING(<columnName>)

-- inner join with ON …

Last month at the Boston MySQL User Group, I went through the meanings of INNER, LEFT/RIGHT OUTER, CROSS, NATURAL joins, how to do a FULL OUTER JOIN in MySQL, and what STRAIGHT_JOIN means. I also explained how to recognize when you want those types of joins, and best practices for the semantics of writing joins and design patterns. Subqueries were explained in this session, and some examples of how to think differently so that you end up writing JOINs instead of subqueries. The slides (slightly different from the slides in the video — due to error correction) can be found at http://technocation.org/files/doc/2010_01MySQLJoins.pdf.

Here’s the video: