Database Reference
In-Depth Information
is also equal to 1000, join all the columns of the first row of RETAIL_ORDER with the columns
from the row you just found in ORDER_ITEM.
For the data in Figure 2-5, the first row of ORDER_ITEM has OrderNumber equal to 1000,
so you join the first row of RETAIL_ORDER with the columns from the first row in ORDER_
ITEM to form the first row of the join. The result is:
Now, still using the OrderNumber value of 1000, look for a second row in ORDER_ITEM
that has OrderNumber equal to 1000. For our data, the second row of ORDER_ITEM has such
a value. So, join FirstName and LastName from the first row of RETAIL_ORDER to the second
row of ORDER_ITEM to obtain the second row of the join, as follows:
Continue in this way, looking for matches for the OrderNumber value of 1000. At this
point, no more OrderNumber values of 1000 appear in the sample data, so now you move to
the second row of RETAIL_ORDER, obtain the new value of OrderNumber (2000), and begin
searching for matches for it in the rows of ORDER_ITEM. In this case, the third row has such a
match, so you combine those rows with the previous result to obtain the new result:
You continue until all rows of RETAIL_ORDER have been examined. The final result is:
Actually, that is the theoretical result. But remember that row order in an SQL query
can be arbitrary, as is shown in the results to SQL-Query-CH02-52 above. To ensure that you
get the above result, you need to add an ORDER BY clause to the query, as shown in SQL-
Query-CH02-53 above.
You may have noticed that we introduced a new variation in SQL statement syntax in
the previous two queries, where the terms RETAIL_ORDER.OrderNumber, ORDER_ITEM.
OrderNumber, and ORDER_ITEM.SKU were used. The new syntax is simply TableName.
ColumnName , and it is used to specify exactly which table each column is linked to.
RETAIL_ORDER.OrderNumber simply means the OrderNumber from the RETAIL_ORDER
table. Similarly, ORDER_ITEM.OrderNumber refers to the OrderNumber in the ORDER_ITEM
 
Search WWH ::




Custom Search