The variables are defined as follows:

select-stmt1 and select-stmt2 are valid SELECT statements.

order-by-clause is an ORDER BY clause that references the columns by number rather than by name.

Here are some requirements and considerations for using the INTERSECT operator:

• The two SELECT statements may not contain an ORDER BY clause; however, you can order the results of the entire INTERSECT operation.
• The number of columns retrieved by select-stmt1 must be equal to the number of columns retrieved by select-stmt2.
• The datatypes of the columns retrieved by select-stmt1 must match the datatypes of the columns retrieved by select-stmt2.
• The optional order-by-clause differs from the usual ORDER BY clause in a SELECT statement because the columns used for ordering must be referenced by number rather than by name. The reason that the columns in the order-by-clause must be referenced by number rather than by name is that SQL does not require that the column names retrieved by select-stmt1 be identical to the column names retrieved by select-stmt2. Therefore, you must indicate the columns to be used in ordering results by their position in the select list.

Consider the following typical scenario for using the INTERSECT operator. As the dean of Flugle College, you suspect that some of Flugle's instructors are also teaching at Hoover College, Flugle's archrival. A list of instructors at Hoover College, archrival of Flugle College, has come into your possession as shown in Listing 8.27. The first query retrieves the instructors at Flugle College. The second query retrieves the instructors at Hoover College. The third query uses the INTERSECT operator to find the common instructors between the two tables.

Listing 8.27. Contents of Instructor table for Flugle and Hoover Colleges.

SQL> select Last_Name, First_Name, MI, Position
     2  from Instructor;

LAST_NAME       FIRST_NAME M POSITION
--------------- ---------- - ------------------------
HITCHCOCK       BORIS        PROFESSOR
DANIELS         LAURA        ASSOCIATE PROFESSOR
EDWARDS         SAMANTHA     PROFESSOR
CHANG           ROGER        ASSISTANT PROFESSOR
JASON           JERROLD      ASSOCIATE PROFESSOR
TORRES          PETER        PROFESSOR

ANGELO          ARTHUR       ASSOCIATE PROFESSOR
RICHARDSON      NANCY        ASSISTANT PROFESSOR
PARKER          WILLIAM      PROFESSOR
CHERNOW         BESS         ASSOCIATE PROFESSOR
CHU             STEVEN       PROFESSOR
WEISS           ROBERTA      PROFESSOR
RESTON          RAPHAEL      ASSOCIATE PROFESSOR
BILLINGS        BENJAMIN     ASSISTANT PROFESSOR
YOUNG           ALLAN        ASSOCIATE PROFESSOR
NILAND          MARTINA      ASSOCIATE PROFESSOR
BATES           JOSEPH       ASSISTANT PROFESSOR
POULSON         RANIER       PROFESSOR

18 rows selected.

SQL> select Last_Name, First_Name, MI, Position
  2  from Hoover_Instructor;

LAST_NAME                 FIRST_NAME       M POSITION
------------------------- ---------------- - --------------------
CHANG                     ROGER               ASSISTANT PROFESSOR
JASON                     JERROLD             ASSOCIATE PROFESSOR
TORRES                    PETER               PROFESSOR
CHERNOW                   BESS                ASSOCIATE PROFESSOR
CHU                       STEVEN              PROFESSOR
RESTON                    RAPHAEL             ASSOCIATE PROFESSOR
YOUNG                     ALLAN               ASSOCIATE PROFESSOR
NILAND                    MARTINA             ASSOCIATE PROFESSOR
BATES                     JOSEPH              ASSISTANT PROFESSOR
HUSTON                    MYRON             T PROFESSOR
RABINOWITZ                KERMIT            A LECTURER
CHRISTIANSON              PAUL              V PROFESSOR

12 rows selected.

SQL> select Last_Name, First_Name, MI, Position
  2  from Instructor
  3  intersect
  4  select Last_Name, First_Name, MI, Position
  5  from Hoover_Instructor;

LAST_NAME                 FIRST_NAME        M POSITION
------------------------- ----------------- - --------------------
BATES                     JOSEPH              ASSISTANT PROFESSOR
CHANG                     ROGER               ASSISTANT PROFESSOR
CHERNOW                   BESS                ASSOCIATE PROFESSOR
CHU                       STEVEN              PROFESSOR
JASON                     JERROLD             ASSOCIATE PROFESSOR
NILAND                    MARTINA             ASSOCIATE PROFESSOR
RESTON                    RAPHAEL             ASSOCIATE PROFESSOR
TORRES                    PETER               PROFESSOR
YOUNG                     ALLAN               ASSOCIATE PROFESSOR

9 rows selected.

If you visually inspect the rows that exist in each table, you can confirm that the two tables do indeed have the nine rows in common.

The UNION Operator

Sooner or later, you'll find yourself in a situation in which you need to combine the rows from similar tables to produce a report or to create a table for analysis. Even though the tables represent similar information, they may differ considerably. To accomplish this task, you should consider using the UNION operator.

The syntax for this set operator is pretty simple:

select-stmt1
UNION
select-stmt2
[order-by-clause]

The variables are defined as follows:

select-stmt1 and select-stmt2 are valid SELECT statements.

order-by-clause is an optional ORDER BY clause that references the columns by number rather than by name.

The UNION operator combines the rows returned by the first SELECT statement with the rows returned by the second SELECT statement. Keep the following things in mind when you use the UNION operator:

• The two SELECT statements may not contain an ORDER BY clause; however, you can order the results of the entire UNION operation.
• The number of columns retrieved by select-stmt1 must be equal to the number of columns retrieved by select-stmt2.
• The datatypes of the columns retrieved by select-stmt1 must match the datatypes of the columns retrieved by select-stmt2.
• The optional order-by-clause differs from the usual ORDER BY clause in a SELECT statement because the columns used for ordering must be referenced by number rather than by name. The reason that the columns must be referenced by number is that SQL does not require that the column names retrieved by select-stmt1 be identical to the column names retrieved by select-stmt2.

To illustrate the use of UNION, imagine that you have been given the task of consolidating information from two seismology labs. The first table is from the Department of Geophysics at the University of Northern South Dakota; the other table is from a private research institution, RIND. Listing 8.28 describes the structure of the two tables.

Listing 8.28. Description of two tables for seismic events.

SQL> desc UNSD_Event
 Name                            Null?    Type
 ------------------------------- -------- ----
 EVENT_NO                                 NUMBER
 EPICENTER_LATITUDE                       NUMBER
 EPICENTER_LONGITUDE                      NUMBER
 EVENT_MAGNITUDE                          NUMBER
 EVENT_TIME                               DATE
 EVENT_WAVE                               CHAR(1)
 INSTRUMENT_NAME                          VARCHAR2(30)

SQL> desc RIND_Event
 Name                            Null?    Type
 ------------------------------- -------- ----
 LOCATION_LAT                             NUMBER
 LOCATION_LON                             NUMBER
 RICHTER_NUMBER                           NUMBER
 DATE_TIME                                VARCHAR2(30)
 WAVE_TYPE                                CHAR(1)

ANALYSIS
Stop for a moment to examine the similarities and differences between UNSD_Event and RIND_Event. First of all, both tables store information about seismic events. However, UNSD_Event has two extra columns: Event_No and Instrument_Name. Both tables store the epicenter latitude and longitude, the magnitude, and the wave type (P or S). However, UNSD_Event defines Event_Time as a DATE, whereas RIND_Event uses VARCHAR2 for storing the event date and time in Date_Time. Listing 8.29 illustrates what happens if you try to perform a UNION without converting Event_Time in UNSD_Event and Date_Time in RIND_Event to a common datatype.

Listing 8.29. UNION operator used with columns with different datatypes.

SQL> select Epicenter_Latitude, Epicenter_Longitude, Event_Magnitude,
            Event_Time, Event_Wave
  2  from UNSD_Event
  3  UNION
    4  select Location_Lat, Location_Lon, Richter_Number, Date_Time, Wave_Type
  5  from RIND_Event;
select Epicenter_Latitude, Epicenter_Longitude, Event_Magnitude,
             Event_Time, Event_Wave
             *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression