SQL Extensions  «Prev  Next»

Intersect Union Minus SQL Commands

The correct commands for the scenarios are:
  1. INTERSECT, Find out which employee has updated at least one record in the PRODUCT table and added at least one record in the PET_CARE_LOG table.
  2. UNION, Find out which customers have purchased a dog in one sale and have purchased a cat in another sale.
  3. MINUS, Determine which products purchased by Amy Black have not been purchased by Lester Lee.


Sometimes you need to combine information of a similar type from more than one table. A classic example of this is merging two or more mailing lists prior to a mailing campaign. Depending on the purpose of a particular mailing, you might want to send letters to any of these combinations of people:
Everyone in both lists (while avoiding sending two letters to someone who happens to be in both lists)
  1. Only those people who are in both lists
  2. Those people in only one of the lists
These three combinations of lists are known in Oracle as UNION, INTERSECT, and MINUS. In the following examples, you will see how to use these three clauses to manage the results of multiple queries.
The examples will compare the books on hand (BOOKSHELF) with those on order (BOOK_ORDER).
To see all the books, UNION the two tables. To reduce the size of the output, only the BOOKSHELF entries from the first half of the alphabet are selected.
The following select returns 14 rows:
select Title from BOOKSHELF
where Title < 'M%';
And this select returns six rows:
select Title from BOOK_ORDER;
If we UNION them together, how many rows are returned?
select Title from BOOKSHELF
where Title < 'M%'
select Title from BOOK_ORDER;