Rules for Multi-Table Query Processing:
List the company name and all orders for customer number 2103.
SELECT COMPANY, ORDER_NUM, AMOUNT
FROM CUSTOMERS, ORDERS
WHERE CUST_NUM = CUST
AND CUST_NUM = 2103
ORDER BY ORDER_NUM
COMPANY ORDER_NUM AMOUNT
-------- --------- ----------
Acme Mfg. 112963 $3,276.00
Acme Mfg. 112983 $702.00
Acme Mfg. 112987 $27,500.00
Acme Mfg. 113027 $4,104.00
To generate the query results for a SELECT statement:
1.If the statement is a UNIONof SELECTstatements, apply steps 2 through 5 to each of the statements to generate their individual query results.
2. Form the product of the tables named in the FROMclause. If the FROMclause names a single table, the product is that table.
3. If there is a WHEREclause, apply its search condition to each row of the product table, retaining those rows for which the search condition is TRUE(and discarding those for which it is FALSEor NULL).
4. For each remaining row, calculate the value of each item in the select list to produce a single row of query results. For each column reference, use the value of the column in the current row.
5. If SELECT DISTINCTis specified, eliminate any duplicate rows of query results that were produced.
6. If the statement is a UNIONof SELECTstatements, merge the query results for the individual statements into a single table ofquery results. Eliminate duplicate rows unless UNION ALLis specified.
7. If there is an ORDER BYclause, sort the query results as specified.
The rows generated by this procedure comprise the query results.
Following the previous steps:
1.The FROMclause generates all possible combinations of rows from the CUSTOMERS table (21 rows) and the ORDERStable (30 rows), producing a product table of 630 rows.
2.The WHEREclause selects only those rows of the product table where the customer numbers match (CUST_NUM = CUST) and the customer number is the one specified (CUST_NUM = 2103). Only four rows are selected; the other 626 rows are eliminated.
3.The SELECTclause extracts the three requested columns (COMPANY, ORDER_NUM, and ORD_AMOUNT) from each remaining row of the product table to generate four rows of detailed query results.
4.The ORDER BYclause sorts the four rows on the ORDER_NUMcolumn to generate the final query results.
