How to join a table many times in a single SQL query statement?

Having more than one join to the same table within a query can be confusing.

More than one join can create a messy SQL statement.

If you don’t use aliases they can be difficult to read.

If your database tables need to have many columns with the same relationship to another table. You can join them with aliases in a single efficient query.

Need a working example, with sample SQL statements? Check out my other post: How to join the same table twice in a single SQL query statement?

For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please subscribe in the box below, for a weekly update of my newest posts every Tuesday!

How to join the same table twice in a single SQL query statement?

Having more than one join to the same table can be confusing.

You might not have even thought it was possible to have multiple joins to the same table.

It is possible and easy to do with the help of aliases.

Aliases for table names

Aliases allow you to reference the table name with an abbreviation.

For example if you have a table called “products” then you reference that table by creating an alias of “p”.

With MySQL you easily create an alias like this:

SELECT p.id FROM products p

Multiple joins to the same table can quickly create a messy SQL statement. If you don’t use aliases they can become difficult to read.

Alias SQL example:

If your database tables need to have two or more columns with the same relationship to another table. You can easily join them with the help of aliases, as explained below.

For example say you have the following two tables: Users and Products. When a Product is created, the Users id that created it is stored. Then when it’s modified the Users id that modified the record is stored.

Products table fields: Comments
id Primary Key
created_by_user_id Foreign Key to Users table
modified_by_user_id Foreign Key to Users table
Users table fields: Comments
id Primary Key
first_name
last_name

If you want to find out who created the product, you can do that with a simple SQL query like this.

In this example, we use the aliases p for the products table and u for the users table:

SELECT
p.id
, u.firstname AS 'creator_firstname'
, u.lastname AS 'creator_lastname'
FROM products p
INNER JOIN users u
ON p.created_by_user_id = u.id

If you want to find out who modified the product you can do that with a similar query using the same aliases:

SELECT
p.id
, u.firstname AS 'modifier_firstname'
, u.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u
ON p.modified_by_user_id = u.id

But how can you combine both queries to know the user that created and modified the products in one query?

Just join the Users table twice, but you need to use a different alias each time you reference the same table.

In this example, we use the aliases u1 for the first reference and u2 for the second reference:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
INNER JOIN users u2
ON p.modified_by_user_id = u2.id

But what if a product has only been created and hasn’t yet been modified? In which case the modified_by_user_id should be null in your products table.  Then the above query won’t be able to join twice to the users table. Unless it has a user record with a null id, which it shouldn’t.  In this case you need to use LEFT JOIN like this:

SELECT
p.id
, u1.firstname AS 'creator_firstname'
, u1.lastname AS 'creator_lastname'
, u2.firstname AS 'modifier_firstname'
, u2.lastname AS 'modifier_lastname'
FROM products p
INNER JOIN users u1
ON p.created_by_user_id = u1.id
LEFT JOIN users u2
ON p.modified_by_user_id = u2.id

So now you can join the same table twice in single efficient query.

Here are all the SQL statements from above and the CREATE table statements:


For more about MySQL please check out the MySQL terminology guide

Interested in more SQL tips? Please pop your email in the box below and you’ll be sure to receive them!