Team Fly

Page 58

This first join example displays both the city and state details for each customer who has purchased a particular product under a specific promotion. The product ID and quantity sold is also displayed:

SQL> select prod_id, quantity_sold, cust_city, cust_state_province
  2  from   sales, customers
  3  where  sales.cust_id = customers.cust_id
  4  and    prod_id = 117;
   PROD_ID QUANTITY_SOLD CUST_CITY
---------- ------------- ------------------------------ 
CUST_STATE_PROVINCE
----------------------------------------
       117             1 Fort Klamath
OR
       117             1 San Mateo
CA
       117             1 Frederick
CO
. . .

The from clause identified two tables and the where clause joins them with table_name.column_name syntax. In this output, since the length of the CUST_CITY and CUST_STATE_PROVINCE columns are too wide for the display, the column headings and results wrap over two lines. Later on in this chapter, we'll take a brief look at the report formatting capabilities of SQL*Plus, which will allow us to control the look of the output.

NOTE
The reason we must adopt the table_name.column_name construct is to tell Oracle exactly which tables and columns to join. This is to avoid any ambiguity when different tables have columns that are named the same.

SQL statements can become quite confusing once you start joining tables, especially when you're joining more than two. Oracle also allows you to give the tables an alternate name known as a table alias. Let's present this query again using ''s" as the table alias for the SALES table and "c" as the table alias for the CUSTOMERS table.

select prod_id, quantity_sold, cust_city, cust_state_province
from   sales s, customers c
where  s.cust_id = c.cust_id
and    prod_id = 117

Team Fly