SQL Joins

What problems do JOINs solve?

Joins help you create a virtual table by combining rows from multiple tables. Whenever you need a virtual table that contains data from multiple tables, JOINs are your friend.

The virtual table mental model

Remember that SQL is a language for specifying a series of transformations to virtual tables using clauses like SELECT or WHERE. Joins are just another SQL clause. They create a new virtual table by matching rows from one table with rows from another table according to a condition.

Sample tables

The rest of this post will assume the following two tables:

posts

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

id	name
1	Michael Scott
2	Jim Halpert
3	Pam Beesly

INNER JOINs

An inner join will keep discard any row combinations that don’t satisfy the JOIN condition.

For example, if we have this query:

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

Then when SQL applies the INNER JOIN transformation to create a new virtual table, it will only keep the rows from the posts table which match the ON p.user_id = u.id JOIN condition. So if for a given posts row, there is no users row where the users.id matches posts.user_id, then that posts row will be discarded from the new virtual table.

For example, the above query will result in this result:

post	author
Michael post 1	Michael Scott
Michael post 2	Michael Scott
Jim post 1	Jim Halpert
Jim post 2	Jim Halpert

Note that the posts records with the titles Anonymous post 1 and Anonymous post 2 aren’t included in the resulting table because those posts rows didn’t satisfy the ON p.user_id = u.id JOIN condition.

No posts from the user Pam (ID: 3) are included in the result either because that user doesn’t have any posts.

LEFT JOINs

A left join is the same as an inner join except it doesn’t discard row combinations that don’t match the join condition. Instead, the columns from the right table will be set to NULL where the join condition fails.

For this example query:

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

The left table is the posts p table and the right table is the users u table.

When SQL applies the LEFT JOIN transformation to create a new virtual table, it’ll set the u.name .column to the matching users.name value if the ON p.user_id = u.id JOIN condition is matched for the left table row. But if the JOIN condition isn’t matched, it’ll simply set u.name to NULL in the resulting virtual table.

For example, the query from above will result in this table:

p.title	u.name
Michael post 1	Michael Scott
Michael post 2	Michael Scott
Jim post 1	Jim Halpert
Jim post 2	Jim Halpert
Anonymous post 1	NULL
Anonymous post 2	NULL

Note that unlike with the INNER JOIN query from earlier, the posts records with the titles Anonymous post 1 and Anonymous post 2 are included in the result even though the ON p.user_id = u.id JOIN condition isn’t met. In this case, we simply set the u.name column to NULL.

Note also that the user Pam Beesly (ID: 3) isn’t included in the result because she doesn’t have any posts records.

If want to list all users along with their posts, even if they don’t have any posts, we can set the base table to users like so:

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

This gives us the following result:

u.name	p.title
Michael Scott	Michael post 1
Michael Scott	Michael post 1
Jim Halpert	Jim post 1
Jim Halpert	Jim post 2
Pam Beesly	NULL

Note that in the result table we do see Pam Beesly even though she has no associated posts record because the users table was the left table. This illustrates why the table order matters when using a LEFT JOIN.