Learn about SQL Joins on Multiple Columns
By: Jim Evans | Updated: 2021-12-09 | Comments (2) | Related: More > 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: 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.
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
View all my tips
Article Last Updated: 2021-12-09