SQL Basics - Java Database Programming Bible - page 91

The result set which will be returned by either statement is:

First_Name Last_Name Order_Number

Fredo Corleone 2

Francis Corleone 3

Vito Corleone 4

Kay Adams 5

For example, the Ordered Items Table provides a link between the order number and
the items in the Inventory Table. To get a detailed list of the inventory items
corresponding to order number 2, you can write the following SQL JOIN command:

SELECT Orders.Order_number, Ordered_Items.Item_number,
Ordered_Items.Qty, Inventory.Name,
Inventory.Description
FROM Orders, Ordered_Items, Inventory
WHERE Orders.order_number = Ordered_Items.order_number AND
Inventory.Item_Number = Ordered_Items.Item_Number AND
Orders.order_number = 2;

Notice how the columns used in the WHERE clause comparison are the key columns
of the various tables. This yields the following ResultSet:

Order_number Item_number Qty Name Description

2 1001 2 Corn Flakes Cereal

2 1004 1 Oatmeal Cereal

Non-Equi-Joins

In addition to Equi-Joins, you can do Non-Equi-Joins, Joins where the relationship is
not equal, though they are not very common. For example, since there are only two
orders in the Orders Table, you can get the other order using the Non-Equi-Join, as
shown here:

SELECT c.Last_Name + ', ' + c.First_Name AS Customer, oi.Qty,
i.Name, i.Description, i.Cost * 1.6 AS Price_Each,
i.Cost * 1.6 * oi.Qty AS Price
FROM Orders o, Customers c, Ordered_Items oi, Inventory i
WHERE o.Order_number = oi.Order_number AND
c.Customer_Number = o.Customer_Number AND
i.Item_Number = oi.Item_Number AND o.Order_number <> 2;