SQL Inner Join Tutorial

The SQL Inner join clause is used combine matching rows from two tables. You can use more than one join clause in your query to combine three or more tables.

In this tutorial let’s dive deeper into how to use Inner join. We’ll see how to join two or more tables make our statement easier to read using aliases.

All of the examples in this tutorial are based on PizzaDB. You can get the script to build the PizzaDB here.

In the previous lesson we looked at how to combine rows from the Customer and CustomerOrder table using the following SQL:

/* Answer */
select LastName, PhoneNumber, Email, OrderDate
from CustomerOrder
   inner join Customer on CustomerOrder.CustomerID = Customer.CustomerID

Let’s have you try an example. Rather than joining from CustomerOrder to Customer, as we did above, rewrite the query to join from Customer to CustomerOrder. Do you think your query provides the same results?

/* Answer */
select LastName, PhoneNumber, Email, OrderDate
from Customer
   inner join CustomerOrder on Customer.CustomerID = CustomerOrder.CustomerID

Let’s now take a deeper looking into the SQL syntax.

Here is the syntax for joining two tables together:

SELECT Table1.Column1, Table2.Column2, AggregateFunction(Table1.Column3)
FROM Table1
   INNER JOIN Table2 ON Table1.CommonColumn = Table2.CommonColumn

In this example:

Table1 is called main or left table (we join from left to right).

• Table2 is the table we are joining to. It is called the right table.
• INNER JOIN indicates we are including rows from both tables if they match.
• The ON clause “announces” the join condition. Here we match two columns.

Let’s now look at the overall syntax. Here is the syntax incorporating all the items we have learned so far.

SELECT Table1.Column1, Table2.Column2, AggregateFunction(Table1.Column3)
FROM Table1
INNER JOIN Table2 ON Table1.CommonColumn = Table2.CommonColumn
WHERE condition
GROUP BY Table1.Column1
ORDER BY Table1.Column1

The main change here, is that INNER JOIN immediate follows the FROM clause. Also notice you can have incorporate GROUP BY when using a join.

Note: If you are serious about learning more about joins, why not checkout my highly rated course Join Together Now? It is very affordable and can be taken online.

Join Two or More Tables

For this this example let’s suppose you want to know the ProductID and Quantity ordered by each customer. We’ll use this ERD diagram to help:

In to find our the quantities ordered, we need to find a way to relate Customers to CustomerOrderItems. According to our ERD diagram this is can be done by the CustomerOrder table.

In order to do this we can use two joins:

• A join between Customer and CustomerOrder (yellow).
• A join between CustomerOrder and CustomerOrderItem (green)

Now that we know the basic plan to relate the tables, let’s write the query!

/* Answer */
select Customer.LastName, Customer.PhoneNumber, Customer.Email, Customer.City,
    CustomerOrder.OrderDate, CustomerOrderItem.ProductID,
    CustomerOrderItem.Quantity
from Customer
    inner join CustomerOrder on Customer.CustomerID = CustomerOrder.CustomerID
    inner join CustomerOrderItem on CustomerOrder.CustomerOrderID = CustomerOrderItem.CustomerOrderID

Notice that the second join is just below the first. If you read the two carefully you can see how the relationship goes from Customer, to CustomerOrder, to CustomerOrderItem.

Can you see it?

If you want to make your query easier to read and maintain you can use table aliases. By doing so you won’t have to keep typing out the full table name. Here is the the from using table aliases:

/* Answer */
select c.LastName, c.PhoneNumber, c.Email, c.City,
    co.OrderDate, ci.ProductID,
    ci.Quantity
from Customer c
    inner join CustomerOrder co on c.CustomerID = co.CustomerID
    inner join CustomerOrderItem ci on co.CustomerOrderID = ci.CustomerOrderID