Page 110
Listing 5.5L_05_05.sqlJoining Tables when the Value Is Contained in Only One of the Tables
SQL> select department_name, count(*) 2 from employee, department 3 where fk_department = department 4 group by department_name;
DEPARTMENT_NAME COUNT(*) --------------- --------- INTERIOR DESIGN 6 POLITICAL SCIEN 7 WELFARE BUREAU 6
NOTE |
This is a very common problem when joining tables. The base table has a value and the related table does not. Oracle has a number of tools that help you retrieve the data sets you actually want. One of these is the outer join, discussed in more detail in the next section. You can use the outer join to correct the problem of the missing department in Listing 5.5. The count (distinct department_number) also corrects the problem. I have made these mistakes countless times in my queries, so I'd like to caution you to always double check your results before giving them to others. This may save you some embarrassment. |
The last potential problem topic to discuss is performance. It is always more efficient to join columns that have been indexed. The RDBMS will use the indexes to match records instead of performing tablespace scans. A tablespace scan is a procedure where the rdbms reads every byte in a table. They should be avoided like the plague. An index scan will reduce the time needed to locate records. Sometimes I have had to use a substr function on a value in the join condition. When you alter the value, you also destroy the capability to use the item's index. This hurts performance. You cannot correct these problems by using SQL techniques. You must correct them when designing the database. When designing your tables, therefore, remember to look at the join columns to be certain that they are properly formatted for maximum performance.
TIP |
When designing tables, be sure that join columns have the same format and length. A partial value in varchar2(7) column will not match a char(7) format. |
When joining tables, sometimes you want to retrieve records from one of the tables even though it does not have a matching record in the other table. You cannot accomplish this with a regular join because the join operator is an equal sign and the conditional statement must evaluate to true to be selected. If a record has a value that does not exist in the join column of
Page 111
the other table, Oracle assigns a null value to the missing column value. When the condition evaluation is done, the result is false and the record is not selected. In the practice database, if you join the department table to the employee table, you cannot see the Census department listed because it currently has no To select the Census department along with the other departments, you can use a technique called an outer join. An outer join is used by Oracle7 to select records that do not have a matching record in the related table. Oracle enables you to indicate an outer join by placing a plus sign (+) after the name of the column in the where clause that has the missing values. Only one of the columns in the conditional statement may have the symbol. You can use the symbol on multiple condition statements if necessary. The columns expected to be retrieved from the missing record will have null values. Listing 5.6 illustrates an outer join.
Listing 5.6L_05_06.sqlJoining Tables with an Outer Join
SQL> select department_name, last_name, first_name 2 from employee, department 3 where department = fk_department(+) 4 order by 1;
DEPARTMENT_NAME LAST_NAME FIRST_NAME --------------- --------------- --------------- CENSUS DEPT INTERIOR DESIGN ROOSEVELT THEODORE INTERIOR DESIGN COOLIDGE CALVIN INTERIOR DESIGN FORD GERALD INTERIOR DESIGN TRUMAN HAROLD INTERIOR DESIGN EISENHOWER DWIGHT INTERIOR DESIGN BUSH GEORGE POLITICAL SCIEN JOHNSON LYNDON POLITICAL SCIEN JOHNSON ANDREW POLITICAL SCIEN CLINTON WILLIAM POLITICAL SCIEN KENNEDY JOHN POLITICAL SCIEN NIXON RICHARD POLITICAL SCIEN WILSON WOODROW POLITICAL SCIEN ROOSEVELT FRANKLIN TREASURY DEPAR WELFARE BUREAU TAFT WILLIAM WELFARE BUREAU REAGAN RONALD WELFARE BUREAU ANTHONY SUSAN WELFARE BUREAU HOOVER HERBERT WELFARE BUREAU CARTER JIMMY
DEPARTMENT_NAME LAST_NAME FIRST_NAME --------------- --------------- --------------- WELFARE BUREAU ROOSEVELT ELEANOR
21 rows selected.
Page 112
NOTE |
My students often ask me how do you know which of the tables should have the outer join symbol? The answer is the table that contains the column as a foreign key. This table is the related table. In a relational database, the record in this table is generally optional, thus there is a chance that the base table may not have a matching value in the related table. |
Relational databases were founded on set theory. In set theory, combinations of observations in different sets are described through the use of union and intersect symbols. Oracle uses these same terms, providing three set operators used to acquire data or records from multiple sets.
The set operators are Union, Intersect, and Minus. The Union operator combines records returned from two select statements. The Intersect operator deducts records from the two sets that do not exist in both sets. The Minus operator returns the records that exist in the first set but do not exist in the second set. The following sections illustrate these operators.
The Union operator combines data from the two select statements it separates. If the two sets have duplicate records or records with the same value, the duplicates are eliminated. The set operators are concerned only with the virtual records and columns returned by the select statements. When two records have the same values in the returned columns, one of the records is eliminated even if it has columns in the database table that are different. The Union operator does not display duplicate records, and you do not have to make certain that the two select statements have mutually exclusive where clauses.
TIP |
I find this operator extremely useful when I want to retrieve records from a table and I want to add different text literals to sets of the records. The union operator enables me to acquire multiple sets of records from the table, add different literals, and combine them into a virtual table for presentation. Listing 5.7 is an example of this procedure. The employee database has employees that were born in the nineteenth and twentieth centuries. The employees in the latter set will have the literal "Born Prior to 1900" added to the record. Records in the latter set will have the literal "Born After 1900." |
Listing 5.7L_05_07.SQLCombining Tables Using the Union OperatorSQL> Column Bday Format A15
SQL> column birth_date noprint SQL> select `BORN PRIOR TO 1900' a, last_name, 2 first_name, to_char(birth_date, `DD-MON-YYYY') bday, 3 birth_date 4 from employee 5 where birth_date < to_date('01-JAN-1900', `DD-MON-YYYY') 6 union 7 select `BORN AFTER 1900' b, last_name, 8 first_name, to_char(birth_date, `DD-MON-YYYY') bday,