Join 3 Tables in SQL

By:   |   Updated: 2021-11-17   |   Comments   |   Related: More > 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.

sql join 3 tables query results

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.

sql join 3 tables query results

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.

sql join 3 tables query results
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.

Additional tips and tutorials:






get scripts

next tip button



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

View all my tips


Article Last Updated: 2021-11-17

Comments For This Article

















get free sql tips
agree to terms