How do SQL joins work?

Example database

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

posts table

id	title	content	user_id
1	Michael post 1	Lorem ipsum	1
2	Michael post 2	Lorem ipsum	1
3	Jim post 1	Lorem ipsum	2
4	Jim post 2	Lorem ipsum	2
5	Anonymous post 1	Lorem ipsum	NULL
6	Anonymous post 2	Lorem ipsum	NULL

users table

id	name
1	michael@example.com
2	jim@example.com
4	pam@example.com

Inner joins

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

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

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

Note how the anonymous posts (i.,e 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 you want to select only records that have a corresponding record from another table. For example, you may want to query all students that have already been assigned to a supervisor.

Left joins

A left join returns all records from the left table along with any matching records from the right table. Unlike inner joins though, left joins will include all results regardless of whether there is a matching record from the right table. Where there are no matching records from the right table, their column values will be set to NULL.

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

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

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.

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

author	post
michael@example.com	Michael post 2
michael@example.com	Michael post 1
jim@example.com	Jim post 2
jim@example.com	Jim post 1
pam@example.com	NULL

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 are typically 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:

author	post
michael@example.com	Michael post 2
michael@example.com	Michael post 1
jim@example.com	Jim post 2
jim@example.com	Jim post 1
pam@example.com	NULL

Wrapping up

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

• Visual Representation of SQL Joins – CodeProject
• SQL JoinsW3 Schools

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