Page 107
Listing 5.2 L_05_02.sqlJoining 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.
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.TXTJoining 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.
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. |
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.sqlJoining 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 |
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.