Listing 8.13. Retrieving hierarchical information from a table.

SQL> select lpad(` `,2*(level-1)) || Assembly_ID Assembly_ID,
  2  Product_ID, Description
 3  from Product_Assembly
  4  start with Product_ID = `X1000'
  5  connect by prior Product_ID = Assembly_ID;
ASSEMBLY_ID  PRODUCT_ID   DESCRIPTION
------------ ------------ ------------------------------------------
             X1000        Complete camera
  X1000      L100         100mm lens - standard
  X1000      B200         Black body, stainless steel frame camera body
    B200     S42          Variable-speed shutter, standard
    B200     I101         Titanium alloy, teflon-coated iris
    B200     W123         Manual film advance, winder
  X1000      S04          4 foot camera strap, leather
  X1000      F100         Blue filter - standard
  X1000      F55          Xenon flash unit
9 rows selected.

ANALYSIS
To indent each row to indicate its hierarchical level, I embedded LEVEL inside the LPAD function so that the number of blanks returned by LPAD corresponds to the hierarchical level. (LEVEL is a pseudocolumn that returns the hierarchical level for each row—from 1 for the highest level to N for the most detailed level.) LPAD is concatenated with Assembly_ID.

To look at a particular subassembly, specify the Assembly_ID in the START WITH clause. The SELECT statement contained in Listing 8.14 illustrates this procedure; it also shows the value of LEVEL for each row.

Listing 8.14. Using LEVEL to indent the output of a hierarchical query.

SQL> select lpad(` `,2*(level-1)) || Assembly_ID Assembly_ID,
      2  Product_ID, Level, Description
     3  from Product_Assembly
     4  start with Product_ID = `B200'
     5  connect by prior Product_ID = Assembly_ID;

ASSEMBLY_ID    PRODUCT_ID LEVEL DESCRIPTION
-------------- ---------- ----- ---------------------------------------------
X1000          B200           1 Black body, stainless steel frame camera body
  B200         S42             2 Variable-speed shutter, standard
  B200         I101             2 Titanium alloy, teflon-coated iris
  B200         W123           2 Manual film advance, winder

You should be aware of a couple of issues when using a hierarchical SELECT statement. First, a hierarchical SELECT statement cannot also be used to join two or more tables (covered later in this lesson). Second, if you specify an ORDER BY clause, you destroy the hierarchical ordering of the rows returned by the query.

Using the EXISTS Operator

In addition to the other SQL operators, you should be familiar with the EXISTS operator. This operator operates on a subquery and returns a Boolean value:

• TRUE if the subquery returns at least one row
• FALSE if no rows are returned by the subquery

Consider the example shown in Listing 8.15, which is based on the Class and Instructor tables. Imagine that you want to determine which instructors, by name, are scheduled to teach a course. Using the EXISTS operator, you submit a query.

Listing 8.15. Using the EXISTS operator.

SQL> select Last_Name, First_Name, Position
    2  from Instructor I
  3  where
   4  exists
   5  (select * from Class C
  6   where
   7   I.Instructor_ID = C.Instructor_ID);

                                                               continues

Listing 8.15. continued

LAST_NAME                FIRST_NAME               POSITION
------------------------ ------------------------ ------------------
CHANG                    ROGER                    ASSISTANT PROFESSOR
JASON                    JERROLD                  ASSOCIATE PROFESSOR
TORRES                   PETER                    PROFESSOR
RICHARDSON               NANCY                    ASSISTANT PROFESSOR
PARKER                   WILLIAM                  PROFESSOR
CHERNOW                  BESS                     ASSOCIATE PROFESSOR
CHU                      STEVEN                   PROFESSOR
WEISS                    ROBERTA                  PROFESSOR
RESTON                   RAPHAEL                  ASSOCIATE PROFESSOR

9 rows selected.

The EXISTS operator is useful in situations in which you're not interested in the column values returned by the subquery. Notice how table aliases—C for Class and I for Instructor—were used to reduce the amount of typing and improve the readability of the query. You can also preface the EXISTS operator with the logical operator NOT. For instance, Listing 8.16 illustrates how you can retrieve a list of instructors who are not scheduled to teach a class.

NEW TERM
An alias is an alternative name for a table, column, or expression used in a query. An alias exists only during the query. You use an alias to simplify or clarify a query.

Listing 8.16. Another example of using the EXISTS operator.

SQL> select Last_Name, First_Name, Position
    2  from Instructor I
  3  where
   4  not exists
   5  (select * from Class C
  6  where
   7  I.Instructor_ID = C.Instructor_ID);

LAST_NAME                FIRST_NAME               POSITION
------------------------ ------------------------ -------------------
HITCHCOCK                BORIS                    PROFESSOR
DANIELS                  LAURA                    ASSOCIATE PROFESSOR
EDWARDS                  SAMANTHA                 PROFESSOR
ANGELO                   ARTHUR                   ASSOCIATE PROFESSOR
BILLINGS                 BENJAMIN                 ASSISTANT PROFESSOR
YOUNG                    ALLAN                    ASSOCIATE PROFESSOR
NILAND                   MARTINA                  ASSOCIATE PROFESSOR
BATES                    JOSEPH                   ASSISTANT PROFESSOR
POULSON                  RANIER                   PROFESSOR

9 rows selected.

ANALYSIS
As you can see in line 5, a subquery retrieves the columns from the Class table for each class that the instructor identified by Instructor_ID is teaching. The NOT EXISTS operator will prevent the query from returning Last_Name, First_Name, and Position if the subquery returns any rows.

The Join Operation

Now that you have a working knowledge of SQL, you are ready to delve into the world of joins. A join can retrieve rows from two or more tables that share a common set of values. A relational database would be of little value if it weren't for the join operation.

NEW TERM
A join is a query that retrieves rows from two or more tables. An equi-join is a join that retrieves rows from two or more tables in which one or more columns in one table are equal to one or more columns in the second table.

The join operation is the mechanism that allows tables to be related to one another. A join operation retrieves columns from two or more tables. To join two tables, for example, the retrieval criteria will typically specify the condition that a column in the first table—which is defined as a foreign key—is equal to a column in the second table—which is the primary key referenced by the foreign key. A join's WHERE clause may contain additional conditions. This type of join operation is referred to as an equi-join. For example, in the sample database, you will want to join the Instructor and Department tables so that you can retrieve related information from both tables with a single SQL statement.

The general syntax for the SELECT statement enables you to join more than two tables.

SELECT select-list
FROM table1, table2, ... , tableN
WHERE table1.column1 = table2.column2 and
...
table2.column3 = tableN.columnN
...
additional-conditions

The variables are defined as follows:

select-list is the set of columns and expressions from table1 through tableN.

table1 through tableN are the tables from which column values are retrieved.

column1 through columnN are the columns in table1 through tableN that are related.

additional-conditions are optional query criteria.

Please note that you are not required to reference column1 through columnN in the select-list.