SQL Inner Join Tutorial


The SQL INNER JOIN is the most common join type you’ll use in day to day queries.  It the relational database workhorse. 

The inner joins main purpose is to combine one or more table together into a single result.  The tables combine by matching columns from one table to the next.  Only combination of rows whose columns match are kept. 

Here is the general syntax for SQL INNER JOIN: 

SELECT table1.column1, table2.column2, …
FROM table1
INNER JOIN table2 ON table1.matchColumn = table2.matchColumn

Let’s now write two inner joins, one for two tables, the other for three.  We’ll use the following diagram to for our next examples: 

SQL Inner Join Example Table Relationships

SQL INNER JOIN – Two Tables 

In this example we’re joining the SalesOrderHeader table to SalesOrderDetail.  This is a many to one relationship, as there is one SaleOrderHeader row for one or more SalesOrderDetail rows. 

SELECT soh.AccountNumber, soh.OrderDate, sod.CarrierTrackingNumber, sod.ProductID, sod.LineTotal FROM sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
SELECT soh.AccountNumber, 
       soh.OrderDate, 
       sod.CarrierTrackingNumber, 
       sod.ProductID, 
       sod.LineTotal 
FROM sales.SalesOrderHeader soh 
INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID 

Look carefully to see how we join from SalesOrderHeader to SalesOrderDetail.  The join condition is SalesOrderID for both tables.  Notice we alias the two table to make it easier to distinguish the columns as well as make it easier to read the SQL. 

SQL INNER JOIN – Three Tables 

One thing you’ll notice with this query, it that there the orders show the ProductID, but we have don’t know the name.  To get this, let’s join to the product table.  All we do is add another INNER JOIN to our statement. 

SELECT soh.AccountNumber, soh.OrderDate, sod.CarrierTrackingNumber, sod.ProductID, p.Name, sod.LineTotal FROM sales.SalesOrderHeader soh INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
SELECT soh.AccountNumber,  
       soh.OrderDate,  
       sod.CarrierTrackingNumber,  
       sod.ProductID,  
       p.Name,  
       sod.LineTotal 
FROM sales.SalesOrderHeader soh 
INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID  
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID 

To add the third join we included another SQL INNER JOIN clause.  This time we join is from SalesOrderDetail to Product.  Since we’re aiming to get products, the join condition is on ProductID. 

Important Points: 

When working with SQL INNER JOINS keep in mind your match from one table to another may match multiple rows.  Meaning, your result may have more rows in the result that you have in either table. 

When columns don’t match, the query returns zero rows. 

You can influence the matching behavior using different types of joins, such as CROSS JOINS, OUTER JOINS

Additional SQL Join Resources 

To learn more about the joins, check out these useful resources: 

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>