The best way to understand the facilities that SQL provides for multitable queries is to start with a simple request that combines data from two different tables:
“List all orders, showing the order number and amount, and the name and credit limit of the customer who placed it.”
The four specific data items requested are clearly stored in two different tables, as shown in Figure 7-1.
- The ORDERS table contains the order number and amount of each order, but doesn’t have customer names or credit limits.
- The CUSTOMERS table contains the customer names and balances, but it lacks any information about orders.
There is a link between these two tables, however. In each row of the ORDERS table, the CUST column contains the customer number of the customer who placed the order, which matches the value in the CUST_NUM column in one of the rows in the CUSTOMERS table. Clearly, the SELECT statement that handles the request must somehow use this link between the tables to generate its query results.
Before examining the SELECT statement for the query, it’s instructive to think about how you would manually handle the request, using paper and pencil. Figure 7-2 shows what you would probably do:
- Start by writing down the four column names for the query results. Then move to the ORDERS table, and start with the first order.
- Look across the row to find the order number (112961) and the order amount ($31,500.00) and copy both values to the first row of query results.
- Look across the row to find the number of the customer who placed the order (2117), and move to the CUSTOMERS table to find customer number 2117 by searching the CUST_NUM
- Move across the row of the CUSTOMERS table to find the customer’s name (“J.P. Sinclair”) and credit limit ($35,000.00), and copy them to the query results table.
- You’ve generated a row of query results! Move back to the ORDERS table, and go to the next row. Repeat the process, starting with Step 2, until you run out of orders.
Of course this isn’t the only way to generate the query results, but regardless of how you do it, two things will be true:
- Each row of query results draws its data from a specific pair of rows, one from the ORDERS table and one from the CUSTOMERS table.
- The pair of rows are found by matching the contents of corresponding columns from the tables.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.