Join 3 Tables in SQL
By: Jim Evans | Updated: 2021-11-17 | Comments (1) | Related: More > JOIN Tables
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.
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.
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:
- SQL Server Subquery Example
- Avoid SQL Server functions in the WHERE clause for Performance
- SQL Server Developer T-SQL Interview Questions
About the author
View all my tips
Article Last Updated: 2021-11-17