Learn about SQL Joins on Multiple Columns

By:   |   Updated: 2021-12-09   |   Comments (2)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > JOIN Tables


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.


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:
     sd.SalesOrderID -- Column Names
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;

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.

result set

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:
     P.Name -- Column Names
    ,(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;

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.

result set
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 categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

Comments For This Article

Saturday, March 25, 2023 - 10:32:53 AM - David Gwyn Smith Back To Top (91046)
excellent article.

Monday, December 27, 2021 - 8:47:28 AM - Jeff Moden Back To Top (89616)
I think you missed the perfect opportunity to introduce the concept of column aliasing so that you can tell things like start dates and end dates apart.

get free sql tips
agree to terms