Team Fly

Page 59

Let's take this join example one step further. cust_id is the column with which we are joining the two tables, and therefore it is found in both the SALES and CUSTOMERS tables. If we want to display cust_id as part of the select list, we would need to prefix it with the table alias:

select s.prod_id, s.quantity_sold, c.cust_id,

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

All the column names in this example were prefixed with the table alias qualifier. While it's only necessary for columns that appear in more than one table, it enhances the readability of the SQL statement as the statements become more complex and include more than one table join.

This leads us into the final example, which presents the concept of joining more than two tables. In addition to joining the CUSTOMERS table to the SALES table as we have in all of the preceding examples, we are also joining the CUSTOMERS table to the PRODUCTS and PROMOTIONS tables so we can pull in columns from those tables, as well.

select c.country_id, p1.promo_name, p2.prod_category, s.quantity_sold,
from   sales s,
       customers c,
       promotions p1,
       products p2
where  s.cust_id = c.cust_id
and    s.promo_id = p1.promo_id
and    s.prod_id = p2.prod_id
and    s.prod_id = 117

It's that simple to join a bunch of tables together. Name each of the tables in the from clause, alias them, and then join them to each other in your where clause using the foreign key relationships.

ANSI Inner Joins

With ANSI joins, the join criteria is found in the from portion of the SQL statement. The where clause only lists the selection criteria for the rows. There are a couple of different ways to join the tables together with ANSI syntax.

ANSI on/using A simple join can be specified with an on or using statement. The columns to be joined on will be listed, and the where clause can list additional selection criteria. The following two examples illustrate the on syntax followed by the using syntax:

select c.cust_id, c.cust_state_province,
       s.quantity_sold, s.prod_category

Team Fly