Getting Started with SQL INNER JOIN


By:   |   Updated: 2021-06-02   |   Comments   |   Related: More > 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.

Vendor table joined to PurchaseOrderHeader table.

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.

query results

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.

database diagram

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.

query results
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.



Last Updated: 2021-06-02


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



Comments For This Article





download





Recommended Reading

UNION vs. UNION ALL in SQL Server

Join SQL Server tables where columns include NULL values

SQL Server Join Example

SQL Server CROSS APPLY and OUTER APPLY

Understanding SQL Server Physical Joins














get free sql tips
agree to terms