How to join multiple tables in one statement with SQL

Learn how to join multiple tables in one statement with SQL with examples and explanations from SQLPad.

Problem

You have multiple tables in your database and you want to join them together in one SQL statement. You want to know how to achieve this in both MySQL and PostgreSQL.

Sample Data

Let's consider three tables: Users, Orders, and Products.

Users table:

UserID	UserName
1	Adam
2	Bella
3	Charlie

Orders table:

OrderID	UserID	ProductID
1	1	1
2	2	2
3	3	3

Products table:

ProductID	ProductName
1	Apples
2	Bananas
3	Cherries

MySQL Solution

SELECT Users.UserName, Products.ProductName
FROM Users
JOIN Orders ON Users.UserID = Orders.UserID
JOIN Products ON Orders.ProductID = Products.ProductID;

PostgreSQL Solution

SELECT Users.UserName, Products.ProductName
FROM Users
JOIN Orders ON Users.UserID = Orders.UserID
JOIN Products ON Orders.ProductID = Products.ProductID;

Explanation

In both MySQL and PostgreSQL, we can use the JOIN keyword to combine rows from two or more tables, based on a related column between them.

In this case, we are joining the Users table with the Orders table based on the UserID. We then join the resulting table with the Products table based on the ProductID. This gives us a result that includes the UserName from the Users table and the ProductName from the Products table for each order in the Orders table.

Please note that this is an example of an inner join which returns records that have matching values in both tables. Different types of joins (LEFT JOIN, RIGHT JOIN, FULL JOIN) would have different outcomes.