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

When a query produces a result table with many rows, we may want to limit the number of
rows that we see. We can do this using the SQL TOP {NumberOfRows} property , which pro-
duces our final SQL query statement:
/* *** SQL-Query-CH02-62 *** */
SELECT TOP 3 RO.OrderNumber, StoreNumber, OrderYear,
OI.SKU, SKU_Description, Department
FROM RETAIL_ORDER AS RO JOIN ORDER_ITEM AS OI
ON RO.OrderNumber=OI.OrderNumber
JOIN SKU_DATA AS SD
ON OI.SKU=SD.SKU
WHERE OrderYear = '2012'
ORDER BY RO.OrderNumber, OI.SKU;
The result of this statement is:
Outer Joins
Suppose that we would like to see how the sales at Cape Codd Outdoor Sports are re-
lated to the buyers—are the buyers acquiring products that sell? We can start with the
SQL-Query-CH02-63:
/* *** SQL-Query-CH02-63 *** */
SELECT OI.OrderNumber, Quantity,
SD.SKU, SKU_Description, Department, Buyer
FROM ORDER_ITEM AS OI JOIN SKU_DATA AS SD
ON OI.SKU=SD.SKU
ORDER BY OI.OrderNumber, SD.SKU;
This produces the result set:
This result is correct, but it shows the names of only five of the eight SKU items in the
SKU_ITEM table. What happened to the other three SKU items and their associated buyers?
Look closely at the data in Figure 2-5, and you will see that the three SKU items and their buy-
ers that do not appear in the results (SKU 100100 with buyer Pete Hansen, SKU 301000 with
buyer Jerry Martin, and SKU 302000 with buyer Jerry Martin) are SKU items that have never
been sold to as part of a retail order. Therefore, the primary key values of these three SKU items