How to Use Set Operators

The set operators in SQL permit you to process the output of two or more queries by combining the result sets, subtracting one result set from another, or displaying the rows that are common to the result sets of each of the select statements.

To learn how to use set operators, follow these steps:

1. You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 before proceeding to the next step.
2. Let's start with a query to display departments with location_id = 1700 (Seattle) with a manager ID of 200 or 205 combined with departments whose names contain the text 'Acc':
   
   I have connect the two queries with the union all set operator. All rows from both result sets are displayed. The Accounting department was selected in each query and therefore appears twice on the report.
3. We can eliminate duplicate rows in the output by simply removing the all keyword:
   
   Now the two queries are connected by the union set operator. The distinct rows from both result sets are displayed.
4. Now we will display only distinct rows in the first result set:
   
   The two queries are connected by the minus set operator. The "Accounting" department has been subtracted from the first result set because that same department appears in the second result set.
5. Finally, let's display only the rows that occur in both result sets:
   
   The two queries are connected by the intersect set operator. The "Accounting" department is the sole line of output because it is the only row common to both result sets. The row appears only one time because intersect displays distinct rows.