Did you know you can do set-based math with SQL Server? With the UNION, INTERSECT, and EXCEPT 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. This is very similar to Oracle Set Operators: UNION, INTERSECT and MINUS

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
• Most implicit conversions will not occur, so cast your values if you need to

Run this to follow along.

create table products (product_id integer, name varchar(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.

EXCEPT

Except (called MINUS in Oracle) 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
except
select * from products where qty > 4
/

PRODUCT_ID NAME           QTY
---------- -------------  ----------
 2         DVD            3
 4         3.5 Diskette   1
 5         USB Keyboard   3
3 rows selected.

Using the UNION, INTERSECT and EXCEPT 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
except
select * from products where qty > 4
order by 1;