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:
