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 (the bit just after the <left-table> INNER JOIN <right-table> ON clause).

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 posts table rows (the “left table”).
2. Include or exclude row based on whether the join condition is met (INNER JOIN users u ON u.id = p.user_id)
   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 the join condition is met, include the row in the result set.
   3. Otherwise, exclude the row from the 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 very useful when you want to combine data from the left table with data from other tables and you want to exclude 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