MySQL JOIN Explained

While selecting data from MySQL server, we can join tables in three basic ways. Using JOINLEFT 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
We decide which join type to use based on data we need from tables. For easier explaination, let’s create an example database test with tables people and attending. SQL create code:
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

For testing purposes will have this data inside the tables:

people
id name age
1 John 20
2 Kim 30
3 Barry 40
attending
id people_id comment
1 1 This is John
2 2 This is Kim
3 4 Does not exist

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:

id name age comment
1 John 20 This is John
2 Kim 30 This is Kim

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:

id name age comment
1 John 20 This is John
2 Kim 30 This is Kim
(null) (null) (null) Does not exist

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:

id name age comment
1 John 20 This is John
2 Kim 30 This is Kim
3 Barry 40 (null)

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
id name age comment
1 John 20 This is John
2 Kim 30 This is Kim
3 Barry 40 (null)

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:

id name age comment
1 John 20 This is John
2 Kim 30 This is Kim
(null) (null) (null) Does not exist

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: , , , , , ,

 
 
 

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close