Wednesday, February 17, 2016

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.

 

.

No comments: