Set Operators

You can combine two SELECT statements using set operations. Here are the valid choices for the operation:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

UNION and UNION ALL combine the results from both queries. UNION will remove the duplicates. UNION ALL will not. INTERSECT gives you what is common to both results. And MINUS gives you stuff from the second results that were not in the first results.

I have used UNION and UNION ALL before. Normally I choose UNION unless there is some business restriction that wants me to filter out the duplicates. Here is a trivia fact: INTERSECT will also filter out any duplicates.

You cannot perform set operations on columns of type BLOCK or CLOB. You can only do an ORDER BY at the end of the whole set operation. The ORDER BY can be by position, or reference an alias from the first query.