By: Jim Evans | Updated: 2021-12-09 | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > JOIN Tables
Problem
Often when writing T-SQL queries the SQL database design may require that you join on more than one column to get accurate results. In this tutorial we will look at a couple examples.
Solution
Multiple column joins may be required based on the database design. Things to look for to identify scenarios that require multi column joins include compound primary keys and foreign keys from multiple columns across multiple tables. The example SELECT statements I will show include scenarios where history tables are populated by triggers and will require multi column joins. The Microsoft AdventureWorks database will be used in the following example queries to JOIN table.
Example 1 – SQL Join on 3 Columns in SQL Server
In AdventureWorks there are triggers that insert all updates to [Sales].[SalesOrderDetail] to a TransactionHistory table and later a process that archives those records to TransactionHistoryArchive. Joining these database tables will require a multi-column join!
In this example I show a query that joins the SalesOrderDetails table to the TransactionHistoryArchive table using a 3 column join condition.
--1) Three-Column Join Syntax:
SELECT
sd.SalesOrderID -- Column Names
,th.ReferenceOrderID
,sd.[SalesOrderDetailID]
,th.ReferenceOrderLineID
,sd.ProductID
,th.ProductID
,th.ActualCost
,th.TransactionType
FROM [Production].[TransactionHistoryArchive] th
INNER JOIN [Sales].[SalesOrderDetail] sd
ON sd.SalesOrderID = th.ReferenceOrderID
and sd.SalesOrderDetailID = th.ReferenceOrderLineID
and sd.ProductID = th.ProductID
ORDER BY 1, 3;
GO
Example 1 Results
In the query results I include the columns that are in the joins showing the same column values. Also, included are ActualCost and TransactionType columns from SalesOrderDetails.
Example 2 – SQL Join on Multiple Columns
This example SQL statement shows a multi-column join including 3 tables. The Product table is included in the SELECT clause to show the product Name. The table ProductCostHistory and ProductPriceHistory are used to show the difference between the product cost and the price differences or the Profit over time. Note that the StartDate and EndDate join columns are using a range, as the Price History and the Cost history may not be the same.
--2) Product Cost to Price History with the following query:
SELECT
P.Name -- Column Names
,ch.StandardCost
,ph.ListPrice
,(ph.ListPrice - ch.StandardCost) as 'Profit'
,ch.StartDate, ph.StartDate, ch.EndDate, ph.EndDate
FROM [Production].[Product] p -- First table
INNER JOIN [Production].[ProductCostHistory] ch -- Second table
ON ch.ProductID = p.ProductID
INNER JOIN [Production].[ProductListPriceHistory] ph -- Third table
ON ph.ProductID = ch.ProductID
and ph.StartDate <= ch.StartDate
and ph.EndDate >= ch.EndDate;
GO
Example 2 Results
Reviewing the result set you can see on line 2 that for AWC Logo Caps the profit was higher after 2012-05-30. Also, on line 15 you can see the profit was less for HL Mountain Frame Black 38 after 2012-05-30.
Next Steps
This tutorial demonstrates common scenarios of multi-column joins. Review the other articles listed below to learn more about different join types.
- SQL Server Join Operations - INNER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN, SELF JOIN, CROSS JOIN, FULL OUTER JOIN
- Getting started with SQL INNER JOIN
- Learn how to Join 3 Tables in SQL
- Read more about JOINS: SQL LEFT JOIN Examples and SQL RIGHT JOIN Examples
- Read about other SQL Server Join Example
- Learn about SQL FULL OUTER JOIN with Examples
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-12-09
