Databases Reference
In-Depth Information
INTERSECT
There are times when you need to know which rows two tables or queries have in
common. The INTERSECT operator provides this functionality. As with the UNION
operator, the number and types of the columns in the two queries to be compared
must be the same, but the column names can be different. Rows are returned from
an INTERSECT operation only if all columns in the two queries match.
In Scott's widget database, the current employment information is kept in the
EMPLOYEES table, and the previous employment information (when employees
have changed jobs) is kept in the JOB_HISTORY table. The boss wants to find out
which employees have changed departments multiple times and have come back
to work in the department they worked in previously, with the same job title.
Janice knows that she needs to use the EMPLOYEES and JOB_HISTORY tables, and
she decides to use the INTERSECT operator to see if there are current employees
in a particular department and job title that are also in the JOB_HISTORY table.
Janice realizes that a multicolumn join in a WHERE clause may produce similar
results, but she thinks that the INTERSECT method is more straightforward and
easier to use and maintain. Her first query looks like this:
select employee_id, job_id, department_id from employees
intersect
select employee_id, job_id, department_id from job_history;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
176 SA_REP 80
1 row selected.
King looks at this report and thinks that something is amiss. He is sure that
there was another employee besides employee number 176 who has changed job
titles and came back to work with her original job title. Janice realizes that she
is comparing too many columns, and she rewrites her query as follows:
select employee_id, job_id from employees
intersect
select employee_id, job_id from job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
176 SA_REP
200 AD_ASST
2 rows selected.
