sajad torkamani

When I first started working with SQL, I remember having difficulty understanding how joins worked. I just sort of winged it for a while and never took the time to properly understand how they worked.

This post is my attempt to sure up my own understanding and to create a handy reference for my future self and my imaginary readers.

Example database

The rest of this post will assume an example blog database with two tables: posts and users

posts table

idtitlecontentuser_id
1Michael post 1Lorem ipsum1
2Michael post 2Lorem ipsum1
3Jim post 1Lorem ipsum2
4Jim post 2Lorem ipsum2
5Anonymous post 1Lorem ipsumNULL
6Anonymous post 2Lorem ipsumNULL

users table

idname
1michael@example.com
2jim@example.com
4pam@example.com

Inner joins

An inner join returns all records from the left table that have a matching record in the right table.

Illustration of SQL inner join

Example query

Assuming the blog database mentioned at the start of this post, the following query will return all the posts records that have a corresponding record in users:

SELECT
	p.title post,
	u.name author
FROM
	posts p
	INNER JOIN users u ON p.user_id = u.id;

Example result

postauthor
Michael post 1michael@example.com
Michael post 2michael@example.com
Jim post 1jim@example.com
Jim post 2jim@example.com

Note how the anonymous posts (posts with the IDs 5 and 6) that don’t have a user_id aren’t included in the result.

Inner joins are very useful when we want to select only records that have a corresponding record from another table. For example, we may want to query all students that don’t yet have an associated supervisor. From my experience, inner joins are the most commonly used so it’s worthwhile to become familiar with them.

Left joins

A left join returns all records from the left table along with any matching records from the right table. Where there are no matching records from the right table, their column values will be set to NULL.

Illustration of SQL left join

Example query

The following query will return all posts records, even if they don’t have a corresponding record in users. 

SELECT
	p.title post,
	u.name author
FROM
	posts p
	LEFT JOIN users u ON p.user_id = u.id;

Example result

postauthor
Michael post 1michael@example.com
Michael post 2michael@example.com
Jim post 1jim@example.com
Jim post 2jim@example.com
Anonymous post 1NULL
Anonymous post 2NULL

Notice how unlike with the inner join query, the left join returns all six posts (including the anonymous posts) and displays NULL for the columns from the users table for posts that don’t have a corresponding user record.

Right joins

A right join returns all records from the right table along with any matching records from the left table. Where there are no matching records from the left table, their column values will be set to NULL.

Illustration of SQL right join

Example query

The following query will return all users records, even if they don’t have a corresponding record in posts. 

SELECT
	u.name author,
	p.title post
FROM
	posts p
	RIGHT JOIN users u ON p.user_id = u.id;

Example result

authorpost
michael@example.comMichael post 2
michael@example.comMichael post 1
jim@example.comJim post 2
jim@example.comJim post 1
pam@example.comNULL

Notice how we get the user pam@example.com even though she doesn’t have any associated posts.

Right joins seem rarer from my experience because they can and arguably should be written as left joins to improve readability. For example the previous right join query can be re-written as:

SELECT
	u.name author,
	p.title post
FROM
	users u
	LEFT JOIN posts p ON p.user_id = u.id;

This will return the same result:

authorpost
michael@example.comMichael post 2
michael@example.comMichael post 1
jim@example.comJim post 2
jim@example.comJim post 1
pam@example.comNULL

Wrapping up

Here are some links that I found useful for explaining SQL joins.

You might also find it useful to practice your SQL skills using a platform like Codewars.