Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 81

Because there are 3 rows of retail order and 7 rows of order items, there are 3 times 7, or 21,
rows in this table. Notice that the retail order with OrderNumber 1000 has been combined
with all seven of the rows in ORDER_ITEM, the retail order with OrderNumber 2000 has been
combined with all seven of the same rows, and, finally, the retail order with OrderNumber 3000
has again been combined with all seven rows.
This is illogical—what we need to do is to select only those rows for which the
OrderNumber of RETAIL_ORDER matches the OrderNumber in ORDER_ITEM. This is easy to
do; we simply add an SQL WHERE clause to the query:

/* *** SQL-Query-CH02-52 *** */
SELECT *
FROM RETAIL_ORDER, ORDER_ITEM

WHERE RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber;

The result is:

This is technically correct, but it will be easier to read if we sort the results using an ORDER BY
clause:

/* *** SQL-Query-CH02-53 *** */
SELECT *
FROM RETAIL_ORDER, ORDER_ITEM
WHERE RETAIL_ORDER.OrderNumber=ORDER_ITEM.OrderNumber
ORDER BY RETAIL_ORDER.OrderNumber, ORDER_ITEM.SKU;

The result is:

If you compare this result with the data in Figure 2-5, you will see that only the appropri-
ate order items are associated with each retail order. You also can tell that this has been done
by noticing that, in each row, the value of OrderNumber from RETAIL_ORDER (the first col-
umn) equals the value of OrderNumber from ORDER_ITEM (the seventh column). This was
not true for our first result.
You can think of the join operation working as follows. Start with the first row in RETAIL_
ORDER. Using the value of OrderNumber in this first row (1000 for the data in Figure 2-5), ex-
amine the rows in ORDER_ITEM. When you find a row in ORDER_ITEM where OrderNumber