MODULE OF INSTRUCTION

Sets Operators
Welcome to the 10th and last module of this course Database
Management System 1! For this lesson, it covers the different type
of sets operators that is used to retrieve sets of data from 2 or more
table. Sample and demonstration in this lesson uses the AUTHORS
and BORROWER table as shown below:
Table 1.0 Authors

Database Management System 1 1

Week 3 Sets Operator

Sets All set operators either IN ANY or ALL have equal precedence.
When a SELECT statement have multiple set operators, the Oracle
server evaluates the operator from left / top to right / bottom – that is
if there is no parentheses. In order to change the order of evaluation
you may use a parentheses, this is added in the query in order to
specify the order of evaluation in the queries that use the
INTERSECT operator with other set operators.

After completing this lesson, the student should be able to:

1. Describe and define what sets operators are.
2. Make used of sets operator.
3. Apply different SELECT statement into sets.

Set Operators
 Set operators combine the results of two or more component
queries into one result. Queries containing set operators are
called compound queries.

Types of Set Operators

Function Description
Union Rows from both queries after eliminating
duplications
Union Rows from both queries including all duplications
ALL
Intersect Rows that are common to both queries
Minus Rows in the first query that are not present in the
second query

_____________________________________________________________________________________
2
MODULE OF INSTRUCTION
Set Operator Guidelines
• The expressions in the SELECT lists must match in number.
• The data type of each column in the second query must match
the data type of its corresponding column in the first query.
• Parentheses can be used to alter the sequence of execution.
• ORDER BY clause can appear only at the very end of the
statement.

Oracle Server and Set Operators

• The duplicated rows are automatically eliminated in the final
report except in UNION ALL.
• The column names selected from the first query appear in the
result.
• By default, the output is sorted in ascending order except in
UNION ALL.

Union Operator
The UNION operator returns all rows that are selected by either
query. Use the UNION operator to return all rows from multiple
tables and eliminate any duplicate rows.
Guidelines
• The number of columns being selected must be the same.
• The data types of the columns being selected must be in the
same data type group (such as numeric or character).
• The names of the columns need not be identical.
• UNION operates over all of the columns being selected.
• The NULL values or blank data in the report are not ignored
during duplicate checking.
By default, the output is sorted in ascending order of the columns of
the SELECT clause.

Database Management System 1 3

Week 3 Sets Operator

Authors Borrower

Example: Retrieve the record on both tables with the same values
after eliminating duplicated data.
SELECT BOOK, AC_NO
FROM AUTHORS
UNION
SELECT BOOK, AC_NO
FROM BORROWER;
Output:

Explanation:
 The UNION operator eliminates any duplicate records. If
records that occur in both the AUTHORS and the BORROWER
tables are identical, the records are displayed only once.
 The example retrieves only the record form AUTHORS and
BORROWER table with match values.

_____________________________________________________________________________________
4
MODULE OF INSTRUCTION
Union ALL

UNION ALL operator is use to return all rows from multiple queries
or multiple tables.

Guidelines
The guidelines for UNION and UNION ALL are the same, except
for the following: unlike UNION, in UNION ALL duplicated rows
are not eliminated in the report and the output is not sorted by
default.

Authors Borrower

Example:
SELECT BOOK, AC_NO
FROM AUTHORS
UNION ALL
SELECT BOOK, AC_NO
FROM BORROWER;

Database Management System 1 5

Week 3 Sets Operator

Output:

Explanation:
 The combination of the two tables totals to 30 rows. The
UNION ALL operator does not eliminate duplicate rows.
UNION returns all distinct rows selected by either query.
UNION ALL returns all rows selected by either query, including
all duplicates.
 The examples retrieves the record form AUTHORS table (equal
to 8 values), and retrieves all record from BORROWER table
(equal to 5 rows) which make the output 13 rows.

Intersect Operator:
Use the INTERSECT operator to return all rows that are common to
multiple queries.

_____________________________________________________________________________________
6
MODULE OF INSTRUCTION
Guidelines
• The number of columns and the data types of the columns
being selected by the SELECT statements in the queries
must be identical in all the SELECT statements used in the
query. The names of the columns, however, need not be
identical.
• Reversing the order of the intersected tables does not alter
the result.
• INTERSECT does not ignore NULL values.

Authors Borrower

Example:
SELECT BOOK, AC_NO
FROM AUTHORS
INTERSECT
SELECT BOOK, AC_NO
FROM BORROWER;
Output:

Explanation:
 In the example in this slide, the query returns only those records

Database Management System 1 7

Week 3 Sets Operator

that have the same values in the selected columns in both tables.

Minus Operator
Use the MINUS operator to return all distinct rows selected by the
first query, but not present in the second query result set (the first
SELECT statement MINUS the second SELECT statement).
Note: The number of columns must be the same and the data types
of the columns being selected by the SELECT statements in the
queries must belong to the same data type group in all the SELECT
statements used in the query. The names of the columns, however,
need not be identical.

Authors Borrower

Example:
SELECT BOOK, AC_NO
FROM AUTHORS
MINUS
SELECT BOOK, AC_NO
FROM BORROWER;

_____________________________________________________________________________________
8
MODULE OF INSTRUCTION
Output:

Explanation:
The output retrieves the record with identical values in AUTHORS
table but not present in the BORROWER table.

Lesson Summary:
In this lesson, you should have learned how to:
• Use the 4 types of SET operator
• Identify the difference between set operator
• Identify the guideline in using set operator.

Terms to Remember!
 Intersect - Rows that are common to both queries
 Minus - Rows in the first query that are not present in the
second query
 Union - Rows from both queries after eliminating duplications
 Union All - Rows from both queries including all duplications

Textbook:
References
 Oracle Press (2010). Applied Oracle Security

References:
 Pratt, Philip J. (2010). Database management systems
 Rob, Peter & Coronel, Carlo (2009). Database Management
Systems

Database Management System 1 9

Week 3 Sets Operator

 Schwalbe, Kathy (2011). Management of Information

Technology Projects
 Wheeler, Evan (2011). Security Risk Management : Building an
Information Security Risk Management Program from the
Ground Up

Supplementary Reading and Video Link

Supplementary Reading
https://oracle-base.com/articles/misc/all-any-some-comparison-
conditions-in-sql
https://www.w3schools.com/sql/sql_any_all.asp
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/some-
any-transact-sql

Supplementary Video
https://www.youtube.com/watch?v=4bglO4qJZcc
https://www.youtube.com/watch?v=O1KVzYHCKDo
https://www.youtube.com/watch?v=HKZigj4c-DQ

Suggested Reading
 SQL Tutorial. In ws3schools, Retrieved from
http://www.w3schools.com/sql/default.asp
 Database management system. In Encyclopedia Britannica,
Retrieved from
http://www.britannica.com/EBchecked/topic/152201/database-
management-system-DBMS.
 SQL. In Encyclopedia Britannica, Retrieved from
http://www.britannica.com/EBchecked/topic/569684/SQL
 Database Administration. In Encyclopedia.com, Retrieved from
http://www.encyclopedia.com/topic/Database_administration.as
px
 SQL. In Encyclopedia.com, Retrieved from
http://www.encyclopedia.com/topic/SQL.aspx
 Tutorialspoint.com

_____________________________________________________________________________________
10
MODULE OF INSTRUCTION
 oracle.com
 apex.oracle.com

Database Management System 1 11