Did you know you can do set-based math with Oracle? With the UNION, INTERSECT and MINUS set operators, you can compare one result set to another and add or subtract results, called a compound query. This can be a handy tool for your toolbox.

First, a couple of things to keep in mind.

• The number of fields in each query must be the same
• The datatypes in the corresponding columns must match or be converted to match
• char will get converted to a varchar2 if the chars are different length, but most implicit conversions will not occur

Run this to follow along.

create table products (product_id integer, name varchar2(30), qty integer);

insert into products values (1, 'CD', 5);
insert into products values (2, 'DVD', 3);
insert into products values (3, 'USB Drive', 10);
insert into products values (4, '3.5 Diskette',1);
insert into products values (5, 'USB Keyboard', 3);
insert into products values (6, 'Telephones', 0);

UNION [ALL]

A union is the combination of two result sets. Adding ALL includes all of the duplicates as well. Otherwise, the results only include the distinct values.

Run two queries joined by a UNION. We will get results from both queries and the duplicates will be removed.

select * from products where qty between 1 and 5
UNION
 
select * from products where qty > 4
PRODUCT_ID NAME          QTY
---------- -----------   ----------
 1         CD            5
 2         DVD           3
 3         USB Drive     10
 4         3.5 Diskette  1
 5         USB Keyboard  3

5 rows selected.

Add ALL to UNION and the duplicates are not removed. Notice how “CD” shows up twice.

select * from products where qty between 1 and 5
UNION ALL
select * from products where qty > 4
/

PRODUCT_ID NAME         QTY
---------- -----------  ----------
 1         CD           5
 2         DVD          3
 3         USB Drive    10
 4         3.5 Diskette 1
 1         CD           5
 5         USB Keyboard 3

6 rows selected.

INTERSECT

The intersect is where the two result sets are the same. Where can they both agree? As we saw from above, there was a duplicate for “CD” when we used UNION ALL because “CD” was a valid result for both queries. Using INTERSECT, it is the duplicates in which we are interested.

select * from products where qty between 1 and 5
intersect
select * from products where qty > 4
/

PRODUCT_ID NAME       QTY
---------- ---------- ----------
 1         CD         5

1 row selected.

MINUS

Minus gets a little trickier. It returns everything from the first query MINUS anything that is also found in the second query. If it is only found in the second query, it is disregarded completely.

select * from products where qty between 1 and 5
minus
select * from products where qty > 4
/

PRODUCT_ID NAME           QTY
---------- -------------  ----------
 2         DVD            3
 3         USB Drive      10
 4         3.5 Diskette   1

3 rows selected.

Using the UNION, INTERSECT and MINUS operators, you can work with and compare sets of data, another tool in your toolbox for querying with SQL.

ORDER BY

In a compound query, the ORDER BY clause must be placed at the end. The clause uses only the columns and data from the first query to sort the data. In the example below, the data returned is sorted by the PRODUCT_IDs returned from the first query.

select * from products where qty between 1 and 5
minus
select * from products where qty > 4
order by 1;