Tuesday, August 11, 2009

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.


.

2 comments:

Danijela said...

This information is very helpful. I was wondering if you could comment on the syntax for inner join with several on clauses, more specifically how do you use parenthesis ?

Roger's Access Blog said...

I'm not exactly sure what you're asking, but the parentheses are used to enclose one of the two joins in order to join the other table to it. In other words, you join one table to another, then you join a second table to that join.

With an equi-join, they don't need to be done in any particular order. The following FROM clauses produce identical results:

FROM Orders INNER JOIN (Products INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID) ON Orders.OrderID = OrderDetails.OrderID;

FROM Products INNER JOIN (Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) ON Products.ProductID = OrderDetails.ProductID;