Performing SELECT Statements that Use Two Tables

Most database schemas have more than one table, with those tables storing different aspects of an enterprise. For example, the store schema has a number of tables that represent different aspects of the store. Up to now, all the queries involved only one database table from the store. However, you might want to get information from more than one table ”for example, you might want to get the name of the product type and display that name along with the name of the actual product itself. In this section, you ll learn how to perform queries that span multiple tables.

Let s consider an example. Assume you want to view the names of the products, along with the name of the product type for each product in the same output. Now, you know that the name of the product is stored in the name column of the products table, and that the name of the product type is stored in the name column of the product_types table. You also know from the previous chapter that the products and product_types tables are related to each other via the foreign key column product_type_id . Just to refresh your memory, the product_type_id column (the foreign key) of the products table points to the product_type_id column (the primary key) of the product_types table.

So, if you select the name and product_type_id columns from the products table for product #1, you have

  SELECT name, product_type_id   FROM products   WHERE product_id = 1;  NAME PRODUCT_TYPE_ID ------------------------------ --------------- Modern Science 1 

And if you select the name column from the product_types table for the product type with a product_type_id of 1, you have

  SELECT name   FROM product_types   WHERE product_type_id = 1;  NAME ---------- Book 

From this, you know that product #1 is a book. Nothing very complicated so far, but what you really want is to view the product name and the product type name on the same line. How do you do this?

The answer is to join the two tables in the query. To join two tables means that you specify both the tables in the query s FROM clause, and then use related columns (a foreign key from one table and the primary key from another table, for example) from each table. You also use an operator ”such as the equality operator ( = ) ”in the query s WHERE clause.

Note  

The tables whose columns are used in the join must also be contained in the FROM clause of the SELECT statement. The columns used in the join are usually related through a foreign key.

So, for the example, the FROM clause would be as follows :

 FROM products, product_types 

The WHERE clause could be as follows:

 WHERE product_type_id = product_type_id 

There s a problem with this WHERE clause: both the products and product_types tables contain a column named product_type_id . You must tell the database which tables to use. To do this, you supply the table names before the column names so that the database knows which columns to use: products.product_type_id and product_types.product_type_id . So the WHERE clause becomes

 WHERE products.product_type_id = product_types.product_type_id 

There s one more problem before you put your final query together: how do you tell the database you want the name columns from both the product_types and products tables? You can t just select the name column because the database wouldn t know which name column you meant . The answer is to include the table names and columns in the SELECT clause ” products.name and product_types.name ”so there s no confusion. So the SELECT clause becomes

 SELECT products.name, product_types.name 

Let s put everything together into a complete SELECT statement and see the returned rows:

  SELECT products.name, product_types.name   FROM products, product_types   WHERE products.product_type_id = product_types.product_type_id;  NAME NAME ------------------------------ ---------- Modern Science Book Chemistry Book Supernova Video Tank War Video Z Files Video 2412: The Return Video Space Force 9 DVD From Another Planet DVD Classical Music CD Pop 3 CD Creative Yell CD 

Perfect! This is exactly what you wanted: the name of the product and the name of the product type. Notice, however, that the product with the name My Front Line is missing from this output. The product_type_id for this product row is null, and because of that the row doesn t appear in the output. You ll see how to include this row later in the section Understanding Outer Joins.

Note  

The join syntax you ve seen in this section uses Oracle s syntax for joins that is based on the American National Standards Institute (ANSI) SQL/86 standard. With the introduction of Oracle9 i , the database also implements the ANSI SQL/92 standard syntax for joins and you ll learn this new syntax later in the section Performing Joins Using the SQL/92 Syntax. You should use the SQL/92 standard in your queries when working with Oracle9i and above, and use SQL/86 queries only when you re using Oracle8i and below.