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.
A Basic Inner Join
In the previous lesson we looked at how to combine rows from the Customer and CustomerOrder table using the following SQL:
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?
Let’s now take a deeper looking into the SQL syntax.
SQL Inner Join 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!
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?
Make Inner Joins Easier to Read
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: