MySQL Joins

Leading on from the earlier, basic introduction to using SQL. MySQL joins are very powerful, but can at times cause confusion. Do you go for a left join, a right join, or an inner join? What is the actual difference between them and how do you use them to pull information from two or more tables?

The Basis

A join is used where you have tables with a relationship, this might be a table that contains your users and one that contains all of the posts on your site. Within the posts table you'd have a column that contains the id of the user that wrote the post, this is a relationship to the id of the user in the users table.
An example of the two tables and their data are below, we'll be using these to demonstrate the different types of joins and the result that they generate. The users table is on the left, and the posts table on the right:

id	username	joined
1	michaelw90	2012-12-01 23:37:10
2	_dte	2012-12-02 15:37:10
3	benhowdle	2012-12-02 16:37:10
4	daryl	2012-12-03 16:37:10
5	joelvardy	2012-12-03 17:37:10

id	title	user	posted
1	Sample Post 1	1	2012-12-01
2	I like turtles	3	2012-12-02
3	My Designz	4	2012-12-03
4	Bacon?	1	2012-12-03
5	I shouldn't exist	6	2012-12-03

For all of the joins we will be joining the posts table on the users table where the user column in posts relates to the id of the users table.

Left Join

This type of join will return all of the data from the left table and NULL where there is no related data in the right hand table. The SQL that we would use would be:

SELECT *
FROM `users`
LEFT JOIN `posts`
ON `users`.`id` = `posts`.`user`

Where there are multiple entries in the posts table for a user, we will see two rows pulled out for that user, with NULL appearing only for values in the posts table:

id	username	joined	id	title	user	posted
1	michaelw90	2012-12-01 23:37:10	1	Sample Post 1	1	2012-12-01
1	michaelw90	2012-12-01 23:37:10	4	Bacon?	1	2012-12-03
2	_dte	2012-12-02 15:37:10	NULL	NULL	NULL	NULL
3	benhowdle	2012-12-02 16:37:10	2	I like turtles	3	2012-12-02
4	daryl	2012-12-03 16:37:10	3	My Designz	4	2012-12-03
5	joelvardy	2012-12-03 17:37:10	NULL	NULL	NULL	NULL

Right Join

A right join is the opposite for the left join, in that it will return NULL for fields that do not appear in the left hand table. The SQL has a very slight change:

SELECT *
FROM `users`
RIGHT JOIN `posts`
ON `users`.`id` = `posts`.`user`

The results will highlight the fact that we have a post from a user that no longer actually exists in the database:

id	username	joined	id	title	user	posted
1	michaelw90	2012-12-01 23:37:10	1	Sample Post 1	1	2012-12-01
3	benhowdle	2012-12-02 16:37:10	2	I like turtles	3	2012-12-02
4	daryl	2012-12-03 16:37:10	3	My Designz	4	2012-12-03
1	michaelw90	2012-12-01 23:37:10	4	Bacon?	1	2012-12-03
NULL	NULL	NULL	5	I shouldn't exist	6	2012-12-03

Inner Join

This is sometimes just referred to as a join, without the word inner, and that is the syntax that we use within the SQL query. The result that we expect with this is only data that is in both tables to be returned - there wont be any NULL values returned. The SQL would be as simple as:

SELECT *
FROM `users`
JOIN `posts`
ON `users`.`id` = `posts`.`user`

This will only pull out all posts for users that exist.

id	username	joined	id	title	user	posted
1	michaelw90	2012-12-01 23:37:10	1	Sample Post 1	1	2012-12-01
3	benhowdle	2012-12-02 16:37:10	2	I like turtles	3	2012-12-02
4	daryl	2012-12-03 16:37:10	3	My Designz	4	2012-12-03
1	michaelw90	2012-12-01 23:37:10	4	Bacon?	1	2012-12-03

Multiple Tables

It is possible to join multiple tables together if so desired. If for example we had a table that contained a list of categories that posts could be in. We could join (on a new column called category) to the id of the category table to get the category name. We can also add in a WHERE clause to only pull out posts for the user _dte:

SELECT *
FROM `users`
JOIN `posts`
ON `users`.`id` = `posts`.`user`
JOIN `categories`
ON `posts`.`category` = `categories`.`id` 
WHERE `username` = '_dte'

Conclusion

Joins can be really helpful, and confusing at the same time. A well structured relational database is key to effectively using joins, as without relationships it is not possible to join one table to another. It is best to check how long your join query takes to run, if it is taking a long time, it might be quicker (and more efficient) to run multiple single SELECT queries that don't contain any joins.