Page 103
This chapter covers different ways of combining
records and tables. The concepts are pertinent for relational
databases because the user needs to assemble the
records from multiple tables. Oracle7 is a straight relational
database, and the concepts apply. Oracle8 is an object-
relational database. If Oracle8 is used as a relational
database these principles apply.
One of the benefits of object design is that the relationships are part of the upfront design. This means the user/developer will not have to apply many of the techniques in this chapter to combine the records. Of course, it will take some time for object databases to appear in the industry. This means the topics in this chapter will be very important for the user/developer. The last section of the chapter describes some of the object technology used by Oracle8. You will have a better grasp of their importance after reading this chapter.
Page 104
In a relational database, the data resides in a number of "related" tables. Tables are related through the use of common columns. The common columns are usually the primary and foreign key columns. To produce information from the database, you must combine these records and tables in different ways. This chapter discusses techniques to combine records from different tables. These techniques include the use of joins to make combined virtual records and combining tables by using the Intersect, Union, and Minus commands.
A join is a relational database technique that takes the record from one table and temporarily combines it with a record from another table. The records are united by virtue of having common values in a column in each table. Figure 5.1 contains two tables that have duplicate columns. The department table has a primary key column of department, and the employee table has a foreign key column of fk_department. These columns contain the same values and can be used to match records for the combined virtual table. A virtual record is an assembled record that exists temporarily in memory only.
A select statement tells the relational database management system (RDBMS) to create a table join. The from clause contains the names of the tables to be joined. A comma (,) separates the table names. The where clause contains one or more conditional statements that contain the name of the common columns that can be used to match records. The statement uses the equal operator (=) to evaluate whether the column value in one table equals the column value in the other table. When this condition is true, the rdbms creates a virtual record in memory of the columns specified in the select clause. You can treat this virtual record as any other record that may have come from one table.
Listing 5.1 demonstrates the use of a join to display each employee's department name. The department name comes from the department table and relates to the employee table through the common columns department and fk_department.
Page 105
FIG. 5.1
Creating a combined
virtual table.
Listing 5.1L_05_01.sqlJoining the Employee and Department Tables
SQL> select department_name, last_name, first_name 2 from employee, department 3 where department = fk_department;
DEPARTMENT_NAME LAST_NAME FIRST_NAME --------------- --------------- --------------- WELFARE BUREAU TAFT WILLIAM INTERIOR DESIGN ROOSEVELT THEODORE WELFARE BUREAU ANTHONY SUSAN WELFARE BUREAU ROOSEVELT ELEANOR INTERIOR DESIGN COOLIDGE CALVIN POLITICAL SCIEN JOHNSON LYNDON WELFARE BUREAU REAGAN RONALD INTERIOR DESIGN BUSH GEORGE POLITICAL SCIEN JOHNSON ANDREW POLITICAL SCIEN CLINTON WILLIAM WELFARE BUREAU CARTER JIMMY INTERIOR DESIGN FORD GERALD POLITICAL SCIEN NIXON RICHARD POLITICAL SCIEN KENNEDY JOHN INTERIOR DESIGN EISENHOWER DWIGHT INTERIOR DESIGN TRUMAN HAROLD POLITICAL SCIEN ROOSEVELT FRANKLIN WELFARE BUREAU HOOVER HERBERT POLITICAL SCIEN WILSON WOODROW
19 rows selected.
Page 106
When the join is requested, Oracle scans the join columns in the two tables, identifying matching records, and retrieving them for the virtual record created by the join. The performance of this process is enhanced if the join columns have been indexed. This consists of defining the column in an index. The rdbms need only to read the index to identify the records to be combined rather than performing tablespace scans.
Forgetting to place the condition statements in the select statement will create a Cartesian join. This type of join combines each record in the first table to each record in the second table. As an example of the cause of performance degradation, if table 1 has 5000 records and table 2 has 3000, the rdbms will create a virtual table of 15,000,000 records. This causes a great deal of processing and memory usage.
NOTE |
A Cartesian join generally produces worthless data. It also is common for an experienced user such as myself to create this type of join inadvertently. So be careful when joining tables. |
TIP |
One technique I use when joining tables is to be certain that I have at least one less join condition than tables to be joined. If I have fewer, I know I am missing a condition. This technique has saved me quite a bit of time in troubleshooting queries. As an example, you might return to Listing 5.1. The from clause contains two tables and the where clause one statement. If the statement had no condition statements, which would violate this tip, the result would be a Cartesian join. |
A column name problem often occurs when joining records. Because each table has a column with the same value, it more than likely is the same column and has the same column name. Duplicate column names that exist in the joined tables in a select clause cause the problem because the RDBMS does not know from which table to retrieve the data. Oracle aborts the query when this happens.
To solve this problem, the column names need to be preceded by a qualifier. It tells the RDBMS which table contains the data. The qualifier can be the name of the table or it can be a table alias. Wherever a duplicate column is used in the select statement, a period (.) separates the qualifier and the name of the column. Listing 5.2 contains an example of a qualifier that uses the name of the table.
The table alias is specified in the from clause of the select statement. By placing the alias immediately after the table name, you name it. The alias can then be used throughout the statement in place of the name of the table. They prove particularly useful when the table names are long, because a short alias eliminates keystrokes. Listing 5.2 illustrates the use of qualifiers and table aliases.