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.

,

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, XML, Windows, Java, Adobe, HTML5, JavaScript, Angular, and much more. Check out our complete course catalog.

Categories

• Database Oracle

Courses

• Advanced Oracle SQL Queries see Class Schedule

Stephen has over 30 years' experience in training, development, and consulting in a variety of technology areas including Java, C, C++, XML, JavaScript, AJAX, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen is a published writer in both technical and non-technical endeavors. Stephen received an undergraduate degree in Computer Science and Physics from Florida State University.