Equi-Join (Inner Join)
This is part 2 of a multi-part series on the SQL Join. For an introduction to the whole topic, take a look at:
What Is A Join: Part 1.
This time I'm going to talk about the Equi-Join, also called an Inner Join. An equi-join combines records from two tables which have common values in both tables and displays only those with matching records. Since an equi-join is the most common type of join, it is often simply referred to as a Join.
Consider the following Order and OrderDetail table
Orders | |
OrderID | OrderNumber |
1 | 111 |
2 | 222 |
3 | 333 |
4 | 444 |
OrderDetails | ||
OrderDetailID | OrderID | Quantity |
1 | 1 | 1 |
2 | 2 | 2 |
5 | 3 | 3 |
6 | 1 | 2 |
7 | 1 | 1 |
8 | 3 | 2 |
9 | 3 | 1 |
Joining them on the OrderID field results in:
Single Join Query | ||
OrderNumber | OrderID | Quantity |
111 | 1 | 1 |
111 | 1 | 2 |
111 | 1 | 1 |
222 | 2 | 2 |
333 | 3 | 3 |
333 | 3 | 2 |
333 | 3 | 1 |
Notice that although the Orders table has a record for Order Number 444, it does not display in Single Join Query. That's because there are no matching records in OrderDetails. An equi-join shows only those records with matching values in both tables.
The SQL for this query is:
SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity
FROM Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID;
In the Query Builder, it looks like this:
Notice the 1 and infinity symbol (∞) on the Join line. These indicate that a Relationship exists between these tables and that the OrderID is a Primary Key for the table on the "One-side". This is important, because if you want a query with a join of two tables to be updateable, the field or fields on the "one-side" of the join must either be a Primary Key or a Unique Index. I'll talk more about the updateability of queries in a later post.
Multiple Joins
A query is not limited to a single join. Each join is between just two tables, but the FROM clause of the query can have many joins. For instance, I can add the Products table
Products | |||
ProductID | ProductName | Cost | Price |
1 | hammer | $1.00 | $2.00 |
2 | nail - 10p | $0.01 | $0.02 |
3 | saw | $5.00 | $10.00 |
4 | wrench | $6.00 | $7.00 |
5 | nail - 8p | $0.01 | $0.02 |
6 | drill | $20.00 | $40.00 |
7 | screw - 1x4 | $0.02 | $0.04 |
8 | hammer | $4.00 | $5.00 |
to the above query to get the product and price for the order.
Something like this:
MultipleJoin | ||||
OrderNumber | OrderID | Quantity | ProductName | Price |
111 | 1 | 1 | hammer | $2.00 |
222 | 2 | 2 | nail - 10p | $0.02 |
333 | 3 | 3 | saw | $10.00 |
111 | 1 | 2 | wrench | $7.00 |
111 | 1 | 1 | saw | $10.00 |
333 | 3 | 2 | hammer | $2.00 |
333 | 3 | 1 | saw | $10.00 |
In the Query Builder, it looks like this:
The SQL is:
SELECT Orders.OrderNumber, OrderDetails.OrderID, OrderDetails.Quantity, Products.ProductName, Products.Price
FROM Products INNER JOIN
(Orders INNER JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID)
ON Products.ProductID = OrderDetails.ProductID;
Next time, I'll discuss the least common type of join, the Cartesian or Cross Join.
.
