Create Simple Join [SQL] - Exercise Result

These two tables are related based on the customer ID, so you will use that as the linking column. Using this information, the SELECT statement becomes

SELECT * FROM Customer, Orders 
WHERE orders.cust_id=customer.cust_id 

Most useful queries request data from two or more tables in a database. For example, these requests for data in the sample database draw data from two, three, and four tables respectively:

1. List the salespeople and the offices where they work (SALESREPS and OFFICES tables).
2. List each order placed last week, showing the order amount, the name of the customer who placed it, and the name of the product ordered (ORDERS, CUSTOMERS, and PRODUCTS tables).
3. Show all orders taken by salespeople in the Eastern region, showing the product description and salesperson (ORDERS, SALESREPS, OFFICES, and PRODUCTS tables).

SQL allows you to retrieve data that answers these requests through multitable queries that join data from two or more tables. These queries and the SQL join facility are described in this chapter. We begin with the join capabilities that have been a part of SQL from the earliest days and are found in all SQL products today. The later sections describe additional join capabilities that first appeared in SQL2 standard and are found in most mainstream products.

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-4 below.

1. The ORDERS table contains the order number and amount of each order, but does not have customer names or credit limits.
2. The CUSTOMERS table contains the customer names and credit limits, 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 use the link between the two tables to generate the corresponding query result. Before examining the SELECT statement for the query, it is instructive to think about how you would manually handle the request, using paper and pencil.