Learn about SQL Joins on Multiple Columns

By:   |   Updated: 2021-12-09   |   Comments (1)   |   Related: More > JOIN Tables


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

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.

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:
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.

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.




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently with Harsco who has managed DBAs, Application and BI Developers and Data Management teams for over 20 years.

View all my tips


Article Last Updated: 2021-12-09

Comments For This 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.


download














get free sql tips
agree to terms