Previous | Table of Contents | Next

Page 107

Listing 5.2 L_05_02.sql—Joining Tables Using Qualifiers and Aliases

SQL> select employee.fk_department, department_name,
  2  last_name
  3  from employee, department dept
  4  where dept.department = employee.fk_department;
FK_D DEPARTMENT_NAME LAST_NAME ---- --------------- --------------- INT INTERIOR DESIGN COOLIDGE POL POLITICAL SCIEN JOHNSON WEL WELFARE BUREAU REAGAN INT INTERIOR DESIGN BUSH POL POLITICAL SCIEN JOHNSON POL POLITICAL SCIEN CLINTON WEL WELFARE BUREAU CARTER INT INTERIOR DESIGN FORD POL POLITICAL SCIEN NIXON POL POLITICAL SCIEN KENNEDY INT INTERIOR DESIGN EISENHOWER INT INTERIOR DESIGN TRUMAN POL POLITICAL SCIEN ROOSEVELT WEL WELFARE BUREAU HOOVER POL POLITICAL SCIEN WILSON WEL WELFARE BUREAU TAFT INT INTERIOR DESIGN ROOSEVELT WEL WELFARE BUREAU ANTHONY WEL WELFARE BUREAU ROOSEVELT 19 rows selected.

In the preceding example, the employee table does not have an alias. Therefore, the table name is used as the qualifier for fk_department_number in the where and select clauses. The department table has an alias called dept. The department table qualifier is used in the where clause.

The example did not contain any duplicated column names, and did not actually need qualifiers. Both tables have a column that contains a department number, but the characters "fk" precede the column in the employee table. This is a common acronym used to denote that the column is a foreign key. Using this type of acronym reduces the need for qualifiers and aliases. This in turn reduces the amount of typing to produce a statement.

Working with Multiple Joins

The number of tables that can be joined is not limited. In fact, you can include the same table in the from clause numerous times. This commonly occurs when you have description tables similar to the department table in a database. I have a system where I am employed that has a work order table with columns for five employee ids. The columns record the person performing a particular step in the approval process. I often have to produce a report that lists these

Page 108

employees by name. Since the names are not recorded on the work order table, I must join the four columns that contain payroll numbers to the employee table five times in order to acquire the employee's last name. Each join retrieves a different last name based on a column in the base table._When I do this, the duplicated table must have a unique qualifier. In this example I use T1, T2, T3, T4, and T5. Listing 5.3 illustrates this technique.

Listing 5.3L_05_03.TXT—Joining the Same Table Five Times

select wo.work_order, t1.last_name, t2.last_name, t3.last_name,
  t4.last_name, t5.authorizer
from work order wo, employee t1, employee t2, employee t3,
     employee t4, employee t5
where wo.requestor = t1.payroll_number
    and wo.preparer = t2.payroll_number
    and wo.reviewer = t3.payroll_number
    and wo.approver = t4.payroll_number
   and wo.authorizer = t5.payroll_number;

This is an example only and cannot be produced from the practice database. The practice database does not have the data fields to support such a five table join.

When joining multiple tables, it is easy to forget join conditions. Remember the rule discussed earlier: When tables are joined by one column only, the number of join conditions should be one less than the number of tables in the from clause.

Unexpected Results with Joining Tables in a One-
to-Many Relationship

When performing joins, sometimes the novice developer (and experienced developer) gets unexpected results. Duplicate records may appear, numeric calculations may be in error, and expected records may disappear. These problems result when join tables have a one-to-many relationship and related value(s) in one of the tables is optional. Suppose, for example, that you want to produce a listing of first and last names of the employees that have purchased tools. One method is to join the employee and tools tables. This results in a listing of the employees that have records in the tools table. Each record that matches the join criteria satisfies the criteria for the listing. Employees that have purchased multiple tools, however, are listed more than once because the rdbms creates a virtual record for each match. The developer may have expected to see one record for each employee with tools, but actually saw more than one. When tables in a one-to-many relationship are joined, the table on the many side determines the number of virtual records created. This can cause a problem if the developer is not careful.

Page 109

TIP
The use of the distinct keyword in the select clause will solve the preceding problem. Another method would be to avoid the join and use a subquery in the where clause. The primary query uses the employee table. The where clause contains an in evaluation operator. The operator uses a subquery that selects the payroll numbers from the tools table.

Inaccurate Calculations Caused by a Table Join

In another example, if you want to calculate the number of departments that have employees purchasing tools, you can use a three table join. The tables will consist of employee, department, and tools. This join will produce a list of the departments. Listing 5.4. shows the result of this query.

Listing 5.4L_05_04.sql—Joining Three Tables and Getting the Wrong Number of Departments

SQL> select count(department)
  2  from employee, department, tools
  3  where department = fk_department
  4    and payroll_number = fk_payroll_number;
COUNT(DEPARTMENT) ----------------- 34

The answer to the preceding query is 34 departments, yet only 5 departments are in the department table. It cannot possibly be the correct answer. The reason for the miscalculation is that the query actually counts the number of records in the tool database, because the joins create a virtual record for each record in the tools database. Each tool record has a matching employee record, and each employee record has a department record.

NOTE
If you would like to see the 34 virtual records, change the select clause in Listing 5.4 to select department, tool_name.n

Problems that Can Occur when the Value Exists in Only One of the Joined Tables

The next example illustrates a query where some records do not appear when expected. The query attempts to display the department names and the number of employees in the departments (see Listing 5.5).

One department, "CENSUS DEPT," does not appear. It was not listed because it does not have any employees and the join condition between the department and employee tables evaluates to false. This causes the record to not appear in the listing.

Previous | Table of Contents | Next