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.
Take our Advanced Oracle SQL Queries course for free.
See the Course Outline and RegisterTo learn how to use set operators, follow these steps:
location_id
= 1700 (Seattle) with a manager ID of 200 or 205 combined
with departments whose names contain the text 'Acc':
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.
all
keyword:
union
set operator. The distinct rows from both result sets are displayed.
minus
set operator. The "Accounting" department has been subtracted from the first result set because that
same department appears in the second result set.
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.
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.