MySQL JOIN Explained

While selecting data from MySQL server, we can join tables in three basic ways. Using JOIN, LEFT JOIN and/or RIGHT JOIN. In this post, I’ll try to put all those three in practice.

Introduction

Basic explanation for all three JOIN types goes as follows:

• (INNER) JOIN: Return only matches
• LEFT JOIN: Return all matches from left table, even if right table does not match
• RIGHT JOIN: Return all matches from right table, even if left table does not match

CREATE TABLE `people` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(25) NOT NULL,
	`age` TINYINT(25) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=MyISAM

CREATE TABLE `attending` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`people_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`comment` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=MyISAM

We have a table called people where all available people are listed. Also, we have a table attending, where all attending people are listed. people_id column is a reference to id column in people table.
After a quick look, you should notice that guy in people table with Id: 3 Name: Barry Age: 40 is not present in attending table and that people_id: 4, present in attending table, does not have a corresponding entry in people table.
Now let’s test some different JOIN types on those two tables.

(INNER) JOIN

The following INNER JOIN queries will list all attending people. No matter which table you select from and then join, the result will be the same. Example queries:

select people.*, attending.comment from attending
inner join people on people.id = attending.people_id

select people.*, attending.comment from people
inner join attending on attending.people_id = people.id

Both queries will return the same data:

Notice that attending entry with people_id: 4 was not returned as in eighter case JOIN conditional expression did not match – or in other words, value 4 was not found in both tables.

LEFT JOIN

Following are an examples for LEFT JOIN. In LEFT JOIN, returned data will always include all data from LEFT table. In our first case, that left table would be attending and in second case people. Here’s an example query where we select from attending table and join people table:

select people.*, attending.comment from attending
left join people on people.id = attending.people_id

Data returned is represented in the following table:

Here we see, that we also get a row in the result from attending table even through it does not have a corresponding row in people table.
If we turn this query around, where we select from people table and then left join attending, we form a query like this:

select people.*, attending.comment from people
left join attending on attending.people_id = people.id

And returned data will look like this:

Now we see, that we got all rows from people table, even if there is no corresponding row in attending table.

RIGHT JOIN

Following are an examples for RIGHT JOIN. In RIGHT JOIN, returned data will always include all data from RIGHT table. In our first case, that right table would be attending and in second case people. Here’s an example query where we select from attending table and join people table:

select people.*, attending.comment from attending
right join people on people.id = attending.people_id

Here we see, that we got all rows from people table, even if there is no corresponding row in attendingtable.
If we turn this query around, where we select from people table and then right join attending, we form a query like this:

select people.*, attending.comment from people
right join attending on attending.people_id = people.id

And returned data will look like this:

Here we see that we also get a row in the result from attending table even through it does not have a corresponding row in people table.

If you look closely, you’ll notice that results from first LEFT JOIN are the same as second RIGHT JOIN and that results from second LEFT JOIN are same as first RIGHT JOIN.

Hopefuly this article will help a bit in understanding different types of JOIN in (my)SQL.

Tags: inner, join, left, MySQL, right, select, SQL

This entry was posted on September 14th, 2011 and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or Trackback from your own site.