Previous | Table of Contents | Next

Page 113

  9    birth_date
 10  from employee
 11  where birth_date >= to_date('01-JAN-1900', `DD-MON-YYYY')
 12  order by 5 desc;
A LAST_NAME FIRST_NAME BDAY ------------------ --------------- --------------- --------------- BORN AFTER 1900 CLINTON WILLIAM 03-APR-1940 BORN AFTER 1900 REAGAN RONALD 01-OCT-1924 BORN AFTER 1900 KENNEDY JOHN 29-MAY-1917 BORN AFTER 1900 CARTER JIMMY 14-JUL-1913 BORN AFTER 1900 FORD GERALD 09-JAN-1913 BORN AFTER 1900 BUSH GEORGE 06-FEB-1911 BORN AFTER 1900 JOHNSON LYNDON 27-AUG-1908 BORN AFTER 1900 NIXON RICHARD 27-AUG-1908 BORN PRIOR TO 1900 EISENHOWER DWIGHT 14-OCT-1890 BORN PRIOR TO 1900 ROOSEVELT ELEANOR 11-OCT-1884 BORN PRIOR TO 1900 TRUMAN HAROLD 08-MAY-1884 BORN PRIOR TO 1900 ROOSEVELT FRANKLIN 30-JAN-1882 BORN PRIOR TO 1900 HOOVER HERBERT 10-AUG-1874 BORN PRIOR TO 1900 COOLIDGE CALVIN 01-JUL-1872 BORN PRIOR TO 1900 ROOSEVELT THEODORE 27-OCT-1858 BORN PRIOR TO 1900 TAFT WILLIAM 15-SEP-1857 BORN PRIOR TO 1900 WILSON WOODROW 28-DEC-1856 BORN PRIOR TO 1900 ANTHONY SUSAN 15-FEB-1820 BORN PRIOR TO 1900 JOHNSON ANDREW 29-DEC-1808
19 rows selected.

When the select statement in Listing 5.7 is performed, the RDBMS performs the first query, and then performs the second query. The results of the two are then compared, the duplicates are eliminated, and the RDBMS combines the records into one virtual table that can then be sorted and displayed. The first expression or column of the virtual table is named after the column or alias in the first column of the first select. The remaining columns take their name from the columns in the first select statement.

The syntax of the statement requires the two statements be separated by the Union operator. The statement can contain as many unioned queries as necessary. Each of the selects must have the same number of columns or expressions, and each of the columns must have the same format as the comparable column in subsequent statements. You cannot mix numeric columns with character columns. The columns do not need to have the same column names or aliases. Oracle uses the column names or aliases from the first select as the name of the columns and the default headings. Each select should have its own group by clause if necessary, but only the last statement can contain the order by clause. The order by at the end of statement orders all the records acquired. Finally, only the last select statement contains the semicolon. Placing this earlier in the statement causes Oracle to execute the statement at that point and to ignore subsequent statements.

Page 114

NOTE
If you would like to select all the rows from the two tables, including the duplicate records, changing the operator to union all causes the rdbms to leave these records in the retrieved set.

Minus

You can use the Minus set operator to extract a set of records that exist in the first group but do not exist in the second group. Oracle performs the two selects and then compares the records. Those that exist in the first set and also exist in second set are eliminated along with the records that exist in the second set only.

This technique proves useful for testing one table to determine whether the value exists in the other. In Listing 5.8, the statement produces a list of departments that do not have any employees.

Listing 5.8L_05_08.SQL—Using the Minus Operator to Discard Common Values

SQL> select department, department_name
  2    from department
  3  minus
  4  select fk_department, department_name
  5  from employee, department
  6  where fk_department = department;
DEPA DEPARTMENT_NAME ---- --------------- CEN CENSUS DEPT TRF TREASURY DEPAR

In this example, you needed to do a join in the second query to produce the same number of columns and values. The same syntax and rules apply to the Minus operator as apply to the Union operator. When you requested the department_name column in the first query, you also had to have the department_name appear in the second query. This means that you had to do a join between the employee and department table to produce the proper number of columns and values that can be evaluated.

TIP
The Minus operator is equivalent to the not in conditional operator. You may find that the Minus operator increases your performance because it does not have to scan the virtual table created by the not in operator for every row of the major select.

Intersect

The intersect operator retrieves records that two select statements have in common. It deletes the records in memory unique to each set. The Intersect operator requires the same

Page 115

syntax and rules as used for the Minus and Union operators. Listing 5.9 illustrates a select statement that retrieves records for employees that have purchased both glasses and tools. This requires the select statement to check the existence of employee records in both the glasses and tools tables. It uses the Intersect operator in a subquery to perform this check.

Listing 5.9L_05_09.SQL—Using the Intersect Operator to Select Common Records

SQL> select last_name, first_name
  2  from employee
  3  where payroll_number in
  4    (select payroll_number from glasses
  5      intersect
  6     select payroll_number from tools);
LAST_NAME FIRST_NAME --------------- --------------- COOLIDGE CALVIN JOHNSON LYNDON REAGAN RONALD BUSH GEORGE JOHNSON ANDREW CLINTON WILLIAM CARTER JIMMY FORD GERALD NIXON RICHARD KENNEDY JOHN EISENHOWER DWIGHT TRUMAN HAROLD ROOSEVELT FRANKLIN HOOVER HERBERT WILSON WOODROW TAFT WILLIAM ROOSEVELT THEODORE ANTHONY SUSAN ROOSEVELT ELEANOR
19 rows selected.

Combining Data with Oracle8

Oracle8 has features that allow the developer to define how data will combined. This eases the burden of combining and retrieving the data later. For instance, if you wanted to produce a report listing the employees names and the eyeglass purchases, a select statement joining the employee and glasses table must be written.

This join can be eliminated using Oracle8. The attributes or fields in the employee and glasses table can be defined into one object. The select statement can be executed against the object. The name of the object is treated as a tablename. Because the object knows how the data

Previous | Table of Contents | Next