SQL JOIN 3 Tables Sample Code

By: Jim Evans | Updated: 2021-11-17 | Comments (1) | Related: > JOIN Tables

Problem

In T-SQL we often have to Join many tables in Microsoft SQL Server databases. This tutorial will show how to Join 3 SQL Server tables for a query. The types of JOINs include INNER JOIN syntax, LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN.

Solution

In this tutorial I will provide 3 examples of 3 table joins. I will use tables from the AdventureWorks database to show the syntax for the join.

Example 1 – SQL Join 3 Tables

In the first example I show a SQL query against the AdventureWorks database that joins 3 different tables. This query shows a common scenario, joining tables that have many to many relationships (Primary Key and Foreign Key). In this case, the JOIN condition is based on inner joins to show Business Contact per Stores. There will be multiple Contacts for some of the stores in the results. This query uses the middle table [BusinessEntityContact] to connect business contacts from table [Person] to business entities which are from table [Store].

--1) 3 Table Inner Join: A = B and B = C
--AdventureWorks Store Contacts SELECT statement
SELECT 
     s.[BusinessEntityID] 
    ,s.[Name] 
    ,p.[Title] 
    ,p.[FirstName] 
    ,p.[MiddleName] 
    ,p.[LastName] 
 
FROM [Sales].[Store] s
   INNER JOIN [Person].[BusinessEntityContact] bec ON bec.[BusinessEntityID] = s.[BusinessEntityID]
   INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = bec.[PersonID]
ORDER BY s.[Name];
GO

Example 1 Results

As you can see from the results below, a store can have more than 1 Business Contact.

Example 2 – SQL Join 3 Tables

In this example, I show a query with JOIN statements where my first table is used to join to two other tables. The query returns Products from the first table and returns two attributes, first is the model name and second is the subcategory name.

--2) 3 table with Inner and Outer join A=B and A=C:
SELECT  
     p.ProductNumber
   , p.Name as [Product_Name]
   , m.Name as [Model_Name]
   , s.Name as [Subcategory_Name]
FROM [PRODUCTION].Product p
   INNER JOIN [PRODUCTION].ProductModel m ON m.ProductModelID = p.ProductModelID
   LEFT JOIN [PRODUCTION].ProductSubcategory s ON s.ProductSubcategoryID = p.ProductSubcategoryID
ORDER BY p.Name
GO

Example 2 Results

The results below list the products with Product Number, Product Name and the attributes Model and Subcategory. Because of the LEFT JOIN to table ProductSubcategory, if the Product did not have a Subcategory, it would still be returned, though the Subcategory Name would be NULL.

Example 3 – SQL Join 3 Tables

In the final example I join the Product table to SalesOrderLines with a FULL JOIN and include the Product Model Name with an INNER JOIN.

--3) 3 table with Inner and Outer join A = B, A = C:
SELECT 
     p.Name
    ,sd.SalesOrderID
    ,m.Name as [Model_Name] 
FROM Production.Product p  
   FULL OUTER JOIN Sales.SalesOrderDetail sd ON p.ProductID = sd.ProductID  
   INNER JOIN [PRODUCTION].ProductModel m ON m.ProductModelID = p.ProductModelID
ORDER BY sd.SalesOrderID; 
GO

Example 3 Results

The results below show the Products that sold and the Products that unfortunately did not sell which show a NULL value for the SalesOrderID.

Next Steps

As you can see, joining 3 tables in a query is not much different then joining 2 tables, but just one additional join clause. You can easily expand to many more joined tables to meet your needs. Though, keep in mind, the more tables you add the slower your query results will be returned, as you are asking the SQL engine to do more work.

Learn more about joins by reviewing some of the other articles listed below.

Getting Started with SQL INNER JOIN
Learn about Right Join Examples
Read more about JOINS: SQL LEFT JOIN Examples
Read about other SQL Server Join Example
Learn about SQL FULL OUTER JOIN with Examples
Download the AdventureWorks sample database

Additional tips and tutorials:

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-11-17

Join 3 Tables in SQL