How to INNER JOIN on the same table twice in SQL statement

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

More than one INNER JOIN can create a messy SQL statement.

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

So join them with 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.

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

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

Table users is aliased as u, products as p.

Then you can prepend the alias to SELECT a field from a table.

p.id is equivalent of writing products.id

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

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 use the same table:

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 subscribe below and you’ll be sure to receive them!

Or follow me on Twitter

What alternatives database engines are there to MySQL?

Unsure what alternatives there are to MySQL?

Looking for increases in MySQL performance?

You might not realize that there are some compatible relational database engines. That you can drop in to replace MySQL.

You can even run SQLite without needing a server stack.

SQLite is the most widely deployed database engine in the world

Each engine has it’s own pros and cons to consider before switching from one to another. Most are available as a drop in replacement for MySQL, but yet dropping out and back to MySQL may not be as smooth. Development of new versions means the code bases and features diverge from each other.

MariaDB, Percona Server and Amazon Aurora all offer performance improvements over vanilla MySQL. All for free:

Relational Database Engine Details Costs Link
Amazon Aurora Amazon Aurora is a MySQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. Amazon Aurora provides up to five times better performance than MySQL with the security, availability, and reliability of a commercial database at one tenth the cost. AWS has a free usage tier, otherwise costs depend on server spec x usage. Amazon Aurora
MariaDB One of the most popular database servers. Made by the original developers of MySQL. Guaranteed to stay open source. Free https://mariadb.org/
MariaDB subreddit
MySQL MySQL is the world’s most popular open source database. Whether you are a fast growing web property, technology ISV or large enterprise, MySQL can cost-effectively help you deliver high performance, scalable database applications. Community Edition is free. MySQL
MySQL subreddit
Percona Server Is a free, fully compatible, enhanced, open source drop-in replacement for MySQL that provides superior performance, scalability and instrumentation. Percona Server’s self-tuning algorithms and support for extremely high-performance hardware delivers excellent performance and reliability. Free Percona Server
Percona Server subreddit
SQLite SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed database engine in the world. Free SQLite
SQLite subreddit