SQL inner join

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

What is an inner join?

An inner join returns only the rows from the left table (the table referenced just before the INNER JOIN keyword) which satisfy the join condition.

Let’s look at an example query to understand better.

Example query

Assuming the blog database mentioned at the start of this post, the following query will return only the posts records which match the join condition (p.user_id = u.id):

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

For clarity, this bit is the join condition:

When processing the above query, the MySQL engine will do something like the following:

1. Iterate through all the rows in the posts table (the “left table”).
2. Include or exclude the posts row depending on whether that row satisfies the join condition (INNER JOIN users u ON u.id = p.user_id). More specifically, it will do something like this:
   1. Try to find a matching record from the users table where the users.id column matches the posts table row currently being iterated.
   2. If a matching users record is found, include the current posts row in the result set.
   3. Otherwise, exclude the posts row from the result set.
3. At the end of the INNER JOIN, you’re left with only the rows in the posts table which satisfy the join condition. As a result of the join, you also have access to the matching users row for each posts row so you can display information from both tables in a single row of your result set.

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 because the join condition isn’t met for those rows).

When to use an inner join?

Inner joins are useful when you want to combine data from the left table with data from other tables whilst aslo excluding any rows from the left table that don’t have matching records in the other tables.

For example, you may want to query all posts that have an author assigned with a query like:

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

In this case, the query results will only include posts records that have a corresponding record from the users table.

Links

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