SQL Join on Multiple Columns Examples

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

Jim Evans

Jim Evans is an IT professional who has worked with SQL Server since 1995. He has a passion for SQL Servers and working with the people who utilize it. As a C++ programmer early in his career he was mentored to learn T-SQL with optimizing for performance always a goal. He has supported many corporate business units and application, taken part in designing many databases, managed DBAs, BI Developer and Data Management teams. He is always up for a challenge, embraces new features and functionalities as they are rolled out and enjoys sharing his knowledge. When not working on SQL Server he has spends time coaching youth sports, coaching in a disability softball league and volunteering with Black Dagger Military Hunt Club providing outdoor activities for veterans.

• MSSQLTips Awards: Trendsetter (25+ tips) – 2022 | Author Contender – 2021 | Rookie Contender – 2019