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, …
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 – 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
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
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.
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.
Additional SQL Join Resources
To learn more about the joins, check out these useful resources:
- Introduction to Database Joins
- SQL Inner Join to Two or More Tables
- What is the Difference Between an Inner and Outer Join?