Getting Started with SQL INNER JOIN

By: Jim Evans   |   Updated: 2021-06-02   |   Comments (1)   |   Related: > JOIN Tables

Problem

SQL Server is a relational database in which tables are designed in a way that they typically have a primary key and relationships between tables is done by defining a foreign key in other related tables. For example, a Vendor table would have a primary key or unique ID assigned to each Vendor record in the table. A related table such as the Purchase Order table would have a reference to the Vendor records by including the unique ID associated with the Vendor as a Foreign key. When writing T-SQL code, how do you join tables such as these to return combined results?

Solution

To join tables in T-SQL you use one of several Join statements. In this tip we will focus on the most basic join type, the INNER JOIN. Other join conditions include LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN and SELF JOIN. These can be read about in other tips reference later in this article. In this article I will use examples from Microsoft’s Sample database AdventureWorks.

What Is INNER JOIN

In T-SQL a Join is the term used for combining records from 2 or more tables which is a equi join. INNER JOIN is the basic standard form of a join. Joining tables is done in the "FROM" clause of a T-SQL statement using the keyword INNER JOIN or JOIN. In this tip I will use the fully spelled INNER JOIN keywords to clearly differentiate from other Join types.

When to Use INNER JOIN

The INNER JOIN is used when you want to join records from tables, and you only want to return rows with matching records. In our first example we will show the use of INNER JOIN clause to return records from Vendors that have Purchase Orders. Any Vendors that do not have Purchase Orders will not be returned.

SQL INNER JOIN Example 1 – Vendor to PurchaseOrderHeader

In this example we will return all PurchaseOrderHeader records with matching rows for each Vendor with a SELECT statement. The Primary Key of the Vendor table (left table) is BusinessEntityId and it references to the VendorID field in the PurchaseOrderheader table (right table). We will use the INNER JOIN syntax and ON specifying the column names from each table we are joining on. Note that I use table aliases as a short cut to denote which table each column in the select is coming from (v for Vendor and po for PurchaseOrderHeader).  Here is the JOIN syntax in the following query:

--Example 1: PurchaseOrders per Vendor
SELECT 
   v.BusinessEntityID,
   v.AccountNumber,
   v.Name,
   po.PurchaseOrderID, 
   po.VendorID, 
   po.OrderDate, 
   po.SubTotal, 
   po.TaxAmt, 
   po.Freight, 
   po.TotalDue
FROM [Purchasing].[Vendor] as v
   INNER JOIN [Purchasing].[PurchaseOrderHeader] as po ON po.VendorID = v.BusinessEntityID
ORDER BY po.OrderDate, v.BusinessEntityID;
GO 

Example 1 Results

Note that the v.BusinessEntityId and the po.VendorID which we joined on have the same values in the results. A partial result set is shown.

SQL INNER JOIN Example 2 – Vendor to Purchase Order with Order Details

In this next example I will build off the first query and using INNER JOIN, will add additional tables to get to the order detail lines and show the Product associated with each order detail line. The diagram below shows the 4 tables that I will be joining with INNER JOIN.

In this example I show four tables joined together using the INNER JOIN.

--Example 2: PurchaseOrders with Details per Vendor
SELECT 
   po.VendorID, 
   v.AccountNumber,
   v.Name,
   po.PurchaseOrderID, 
   pod.ProductID, 
   p.ProductNumber,
   p.Name, 
   pod.OrderQty, 
   pod.UnitPrice, 
   pod.LineTotal
FROM [Purchasing].[Vendor] as v
   INNER JOIN [Purchasing].[PurchaseOrderHeader] as po ON po.VendorID = v.BusinessEntityID
   INNER JOIN [Purchasing].[PurchaseOrderDetail] as pod ON pod.PurchaseOrderID = po.PurchaseOrderID
   INNER JOIN [Production].[Product] as p ON p.ProductID = pod.ProductID
ORDER BY pod.PurchaseOrderID, pod.ProductID;
GO

Example 2 Results

Keying into the results of the 4 joined tables with INNER JOIN, on Vendor Aurora Bike Center, the Purchase Order ID 8 has 5 order line details showing some very expensive lock washer products. A partial result set is shown.

Next Steps

These examples of INNER JOIN are typical of T-SQL that can be used to model table join queries specific to your database needs. Below are references to other SQL tutorials that show more about other types of T-SQL joins.

• All SQL JOIN tutorials on MSSQLTips.com
• Learn more about SQL Server T-SQL
• Read more about JOINS: SQL LEFT JOIN Examples
• Read about Selecting data from multiple SQL Server tables
• Learn about SSQL Server INNER JOINs with WHERE clause and ORDER BY logic

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