What is a JOIN: Part 2 (Inner Join)

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.

 

.