How to Join 3 Tables in SQL: Methods and Examples

While SQL joins are commonly used to combine data from two tables, there's no need to stop there. You can actually merge data from three or even more tables in order to uncover even more complex relationships and insights from your data.

In this article, I will walk you through examples of joining three tables in SQL; we will also consider database-specific implementations and best practices. I also highly recommend taking our Introduction to SQL and Learn SQL courses for all the important foundational knowledge.

The Steps to Join 3 Tables in SQL

Joining three tables in SQL allows you to perform complex queries to retrieve data across multiple tables. When joining three tables, we use the JOIN clause to combine data from these tables. 

Assume you have the following three tables: Customers, Products, and Orders.

Example of Customers table in SQL. Image by Author.

Example of Products table in SQL. Image by Author.

Example of Orders table in SQL. Image by Author.

First, you can join the Customers and Orders tables to show which customers placed orders and their order details, such as order_id, order_date, and the quantity ordered.

-- Select data from Customers
SELECT 
    Customers.customer_id, 
    Customers.first_name, 
    Customers.last_name, 
    Orders.order_id, 
    Orders.order_date, 
    Orders.quantity 
FROM Customers
-- Join Orders table
INNER JOIN Orders 
ON Customers.customer_id = Orders.customer_id;

Example of SQL JOIN Customers and Orders table. Image by Author.

Finally, you can combine the three tables, Customers, Orders, and Products, to retrieve a comprehensive view of customers, their orders, the products they purchased, and the total cost for each order.

-- Select data starting with Customers
SELECT 
    Customers.customer_id,
    Customers.first_name,
    Customers.last_name,
    Orders.order_id,
    Orders.order_date,
    Products.product_name,
    Orders.quantity,
    Products.price,
    (Orders.quantity * Products.price) AS total_cost -- Total cost calculation
FROM Customers
-- Join the Orders table based on customer_id
INNER JOIN Orders 
ON Customers.customer_id = Orders.customer_id
-- Join the Products table based on product_id
INNER JOIN Products 
ON Orders.product_id = Products.product_id;

Example of SQL JOIN three tables. Image by Author.

Let us look at the practical examples of using INNER JOIN, LEFT JOIN, and RIGHT JOIN to join three tables. Check our Introduction to SQL Joins tutorial to learn more about the different types and examples of joins.

Examples of How to Join 3 Tables in SQL

Let us now look at some practical examples by using INNER JOIN, LEFT JOIN, and RIGHT JOIN to join three tables.

If you get stuck on the meaning of any of these joins and want further practice so that you can really master the right techniques, enroll in our Joining Data in SQL course, which will not only help you with SQL joins but will also teach you about relational set theory and subqueries.

Using INNER JOIN

An INNER JOIN retrieves records with matching values in all three tables, filtering out any record that does not match. Using INNER JOIN is ideal when you only need results that have direct relationships in all the tables involved.

Suppose you have three tables: Customers, Orders, and Products. The query below retrieves only the customers who have placed orders and includes details for products associated with each order.

-- Select customer details, order information, and associated product names
SELECT 
    Customers.customer_id, 
    Customers.customer_name, 
    Orders.order_id, 
    Products.product_name 
-- Select from Customers table
FROM Customers
    -- Join Orders table
    INNER JOIN Orders 
        ON Customers.customer_id = Orders.customer_id
    -- Join Products table
    INNER JOIN Products 
        ON Orders.product_id = Products.product_id;

Using LEFT JOIN

The LEFT JOIN returns all records from the first (left) table and any matching records from the other two tables. If there is no match, NULL values are returned for columns from the right table. The LEFT JOIN is useful when retrieving all records from the main table, even if some rows don’t have related data in the other tables.

In the following example, all customers will appear in the result, even if they haven’t placed an order. The data fields will return NULL values if there’s no matching order or product data.

-- Select customer details, order information, and product names
SELECT 
    Customers.customer_id, 
    Customers.customer_name,
    Orders.order_id, 
    Products.product_name
FROM Customers
    -- Join Orders table
    LEFT JOIN Orders 
        ON Customers.customer_id = Orders.customer_id
    -- Join Products table
    LEFT JOIN Products 
        ON Orders.product_id = Products.product_id;

As a note, LEFT JOIN is the same as LEFT OUTER JOIN in SQL. We have a tutorial that provides details: SQL LEFT JOIN vs. LEFT OUTER JOIN.

Using RIGHT JOIN

A RIGHT JOIN retrieves all records from the third (right-most) table and any matching records from the left tables. Although RIGHT JOIN is less common, it’s still helpful when prioritizing records in the rightmost table.

For example, in an inventory analysis, you may want to know which products have orders or customer interactions. All products will be listed in the query below, including any associated order or customer data. If there are products that haven’t been ordered, the order_id and customer_name fields will show NULL.

-- Select customer details, order information, and product names
SELECT 
    Customers.customer_id,
    Customers.customer_name,
    Orders.order_id,
    Products.product_name
 -- Select from Customers table
FROM Customers
 -- Join Orders table
    RIGHT JOIN Orders 
        ON Customers.customer_id = Orders.customer_id
 -- Join Products table
    RIGHT JOIN Products 
        ON Orders.product_id = Products.product_id;

While the examples above demonstrate using a single type of join (INNER JOIN, LEFT JOIN, or RIGHT JOIN), it's entirely possible—and often necessary—to combine different join types within the same query to handle more complex data relationships. For instance, you might use a LEFT JOIN to include all records from one table while using an INNER JOIN for another relationship within the same query.

On that note, we should also say that SQL processes joins sequentially from left to right, so the sequence determines how tables are combined and how NULL values are handled. This is why I started this article by showing the process as a two-step process. Building your query step-by-step and verifying results incrementally is the way to go. LEFT JOIN followed by INNER JOIN is often different from INNER JOIN followed by LEFT JOIN.

Why We Have to Join 3 Tables in SQL

Let's take a second to talk about database design. The reason it's common now to have to make joins across more than two tables is because database normalization is now common. Databases (and really the people who design databases) do this by organizing information into distinct, related tables. This is designed to eliminate redundancy (and for other reasons, also). This database decomposition means that data relevant to different entities—such as the Customers, Orders, and Products tables we looked at above—are stored separately.

In other words, because data is so often stored in a normalized database schema, retrieving information often requires combining data from multiple tables. For this reason, we should know how to write queries that make lots of joins because, simply put, that's how the data is likely structured. Our Database Design course is a good resource on these ideas.

Database-Specific Join Features

While SQL joins are consistent across most relational databases, there are some differences in handling multiple joins involving three tables or more. Let us look at database-specific implementations and considerations to avoid pitfalls and optimization issues.

MySQL

MySQL typically performs well with basic joins but can struggle with complex queries involving multiple tables. MySQL’s EXPLAIN command is helpful for understanding join performance and identifying ways to optimize queries with indexing or query rewriting. MySQL also supports the STRAIGHT_JOIN keyword, which forces the join order, potentially improving query performance in cases where the default join optimization isn’t ideal.

PostgreSQL

PostgreSQL’s powerful optimizer makes it highly efficient for complex joins, even with large datasets. Its support for recursive queries using the WITH RECURSIVE clause enables joining hierarchical data across multiple tables. PostgreSQL's window functions are advanced and can complement joins by allowing row-by-row analysis within a joined dataset.

SQL Server

SQL Server allows specific join hints (MERGE JOIN, LOOP JOIN, and HASH JOIN) to override the default behavior, improving multi-table joins performance. The CROSS APPLY and OUTER APPLY operators in SQL Server are powerful tools for joining tables with table-valued functions, providing more flexibility in multi-table queries.

I recommend taking DataCamp’s Introduction to SQL Server course to understand the different functionalities of SQL Server in querying data. Also, try out our SQL Server Fundamentals skill track to improve your joining tables and data analysis skills. The SQL Server Developer career track will equip you with the skills to write, troubleshoot, and optimize your queries using SQL Server.

Oracle SQL

Oracle supports all standard joins and provides unique options like NATURAL JOIN, which automatically joins tables based on matching column names. Oracle’s support for hierarchical queries with CONNECT BY can sometimes reduce the need for additional joins by handling nested data more efficiently.

Best Practices When Joining 3 Tables in SQL

Joining three tables in SQL can increase query complexity and impact performance, especially as data size grows. The following are best practices and optimization techniques to improve query performance.

• Use Appropriate Indexing: Index columns frequently used in join conditions speed up data retrieval by reducing the amount of data scanned.

• Filter Early Using WHERE and JOIN Conditions: Place filtering conditions directly in the JOIN or WHERE clause to reduce the data that needs to be processed in subsequent joins, improving performance.

• Consider the Join Order and Type: When joining multiple tables, join smaller tables first to minimize the dataset as quickly as possible.  Avoid using LEFT or RIGHT joins if your analysis doesn't require unmatched records.

• Minimize Selected Columns with SELECT: Avoid using SELECT * to include unnecessary columns in the data. Explicitly select the required columns to reduce the data returned by each table, minimizing memory use and improving query performance.

• Use Subqueries or Temporary Tables for Complex Joins: Consider breaking the query into smaller parts using subqueries for very complex joins. For joins that involve repeated complex queries, consider storing intermediate results in temporary tables to save processing time, especially for queries that require extensive calculations.

Conclusion and Further Learning

Joining three tables in SQL allows you to combine data across multiple tables for comprehensive data analysis and database management. Mastering the right techniques will make sure your code is efficient, scalable, and high-performing.

If you want to become a proficient data analyst, check out our Associate Data Analyst in SQL career track to learn the necessary skills. The Reporting in SQL course is also appropriate if you want to learn how to build professional dashboards using SQL. Finally, I recommend obtaining the SQL Associate Certification to demonstrate your mastery of using SQL for data analysis and have your resume stand out.