SET Operators in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

SET Operators in Oracle with Examples

In this article, I am going to discuss SET Operators in Oracle with Examples. Please read our previous article, where we discussed LIKE and NOT LIKE Operators in Oracle with Examples. At the end of this article, you will understand UNION, UNION ALL, INTERSECT, and MINUS Operators with Examples.

Set Operators in Oracle:

SQL set operators allow combining results from two or more SELECT statements. At first sight, this looks similar to SQL joins although there is a big difference. SQL joins tends to combine columns i.e. with each additionally joined table it is possible to select more and more columns. SQL set operators on the other hand combine rows from different queries with strong preconditions – all involved SELECTS must. Joins we are collecting the data from two tables when there is common data. But in set operators the data is not joined, in this, the data is merged

1. Retrieve the same number of columns and
2. The data types of corresponding columns in each involved SELECT must be compatible (either the same or with possibility implicitly convert to the data types of the first SELECT statement).

Types of SET Operators in Oracle:

There are four types of SET Operators available in Oracle. They are as follows:

1. UNION: It Returns all distinct rows selected by either query
2. UNION ALL: It Returns all rows selected by either query, including all duplicates
3. INTERSECT: It Returns all distinct rows selected by both queries
4. MINUS: It Returns all distinct rows selected by the first query but not the second

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group.

If component queries select character data, then the data type of the return values are determined as follows:

1. If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
2. If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.

In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.

Set Operator Guidelines in Oracle:

1. In every result set the data type of each column must be compatible (well-matched) to the data type of its corresponding column in other result sets.
2. The result sets of all queries must have the same number of columns.
3. Parentheses can be used to alter the sequence of execution.
4. In order to sort the result, an ORDER BY clause should be part of the last select statement. The column names or aliases must be found out by the first select statement, or the positional notation
5. Column names from the first query appear in the result.

Advantage of SET operators in Oracle:

1. Use a set operator to combine multiple queries into a single query
2. These operators are used to combine the information of similar data types from one or more than one table.

Restrictions on the Set Operators:

The set operators are subject to the following restrictions:

1. The ORDER BY clause doesn’t recognize the column names of the second SELECT
2. The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.
3. The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
4. Set operations are not allowed on SELECT statements containing TABLE collection expressions.
5. SELECT statements involved in set operations can’t use the FOR UPDATE clause.

SQL statements containing these set operators are referred to as compound queries, and each SELECT statement in a compound query is referred to as a component query. Two SELECTs can be combined into a compound query by a set operation only if they satisfy the following two conditions:

1. The result sets of both the queries must have the same number of columns.
2. The data type of each column in the second result set must match the data type of its corresponding column in the first result set.

Syntax of SET Operators in Oracle:

The generic syntax of a query involving a set operation is:
<Component Query>
{UNION | UNION ALL | MINUS | INTERSECT}
<Component Query>

Examples to understand SET Operators in Oracle:

We are going to use the following EmployeeUK and EmployeeUSA tables to understand the SET Operators in Oracle.

Please use the below SQL Script to create the EmployeeUK and EmployeeUSA tables with the required data.

CREATE TABLE EmployeeUK
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUK VALUES(1, 'Pranaya', 'Rout', 'Male','IT');
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUK VALUES(3, 'Preety', 'Tiwary', 'Female','HR');
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUK VALUES(5, 'Anurag', 'Mohanty', 'Male','IT');
INSERT INTO EmployeeUK VALUES(6, 'Rajesh', 'Pradhan', 'Male','HR');
INSERT INTO EmployeeUK VALUES(7, 'Hina', 'Sharma', 'Female','IT');

CREATE TABLE EmployeeUSA
(
  EmployeeId INT,
  FirstName VARCHAR(20),
  LastName VARCHAR(20),
  Gender VARCHAR(10),
  Department VARCHAR(20)
);

INSERT INTO EmployeeUSA VALUES(1, 'James', 'Pattrick', 'Male','IT');
INSERT INTO EmployeeUSA VALUES(2, 'Priyanka', 'Dewangan', 'Female','IT');
INSERT INTO EmployeeUSA VALUES(3, 'Sara', 'Taylor', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(4, 'Subrat', 'Sahoo', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(5, 'Sushanta', 'Jena', 'Male','HR');
INSERT INTO EmployeeUSA VALUES(6, 'Mahesh', 'Sindhey', 'Female','HR');
INSERT INTO EmployeeUSA VALUES(7, 'Hina', 'Sharma', 'Female','IT');

UNION Operator in Oracle

The UNION operator is used to combine the result set of two or more SELECT statements into a single result set and then eliminates any duplicate rows from the final result set. That means the UNION Operator selects only the distinct values.

Following is the Syntax to use UNION Operator in Oracle.

UNION Operator Example in Oracle:

The following SQL query combines two select statements using the UNION operator. In our example, both the EmployeeUK and EmployeeUSA tables have seven records.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

The above statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. Once you execute the above query, you will get the following result set. Please observe, here we don’t have any duplicate data. Here, in the result set, we got a total of 11 rows out of 14 rows. This is because 3 rows are present in both the result set.

UNION ALL Operator in Oracle

The UNION ALL operator is used to combine the result set of two or more SELECT statements into a single result including the duplicate values. Following is the pictorial representation of UNION ALL Operator.

Following is the Syntax to use UNION ALL Operator in Oracle.

UNION ALL Operator Example in Oracle:

The following query combines the result sets of two select statements into a single result set using the UNION ALL operator in Oracle.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION ALL
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

Once you execute the above UNION ALL query, you will get the following result set. Please observe, here we got all the 14 rows in the result set.

Differences between UNION and UNION ALL Operator in Oracle

The UNION operator returns only distinct rows that appear in either result, while the UNION ALL operator returns all rows. The UNION ALL operator does not eliminate duplicate selected rows. When we use a UNION operator, in order to remove the duplicate rows from the result set, it has to do a distinct operation which is time-consuming. For this reason, UNION ALL is much faster than UNION Operator in Oracle. 

UNION/UNION ALL with ORDER BY Clause in Oracle

The UNION/UNION ALL Operator can be used with the ORDER BY clause to sort the result returned from the query. Suppose we want to sort the employees by First Name column values. ORDER BY clause should be part of the last select statement. The SQL statement will be:

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
UNION
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA
ORDER BY FirstName;

Now when you execute the above query and you will get the following output. Here you can see the employees are sorted according to their FirstName column values.

INTERSECT Operator in Oracle

The INTERSECT operator in Oracle is used to combine two result sets and returns the data which are common in both the result set. That means the INTERSECT Operator returns only those rows that are common in both the result sets. Following is the pictorial representation of INTERSECT Operator.

Following is the syntax of INTERSECT operator in Oracle.

INTERSECT operator Example in Oracle:

The following query combines the result sets of two select statements into a single result set using the INTERSECT operator in Oracle.

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
INTERSECT
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

The above statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries. Once you execute the above INTERSECT query, you will get the following result set. Please observe, here we got only 3 rows in the result set which are common in both the result set.

MINUS Operator in Oracle

The MINUS operator in Oracle is used to return unique rows from the left query which isn’t present in the right query’s results. That means the MINUS Operator takes the result set of the first select statement and removes those rows that are returned by a second select statement. Following is the pictorial representation of the MINUS Operator.

Following is the syntax of the MINUS operator in Oracle.

MINUS Operator Example in Oracle:

The following SQL Query will return the unique rows from the left query (the select statement before the MINUS operator) that is not present in the right query (the select statement after the MINUS operator).

SELECT FirstName, LastName, Gender, Department FROM EmployeeUK
MINUS
SELECT FirstName, LastName, Gender, Department FROM EmployeeUSA;

The above statement combines results with the MINUS operator, which returns only unique rows returned by the first query but not by the second. Once you execute the above MINUS Operator query, you will get the following result set. Please observe, here we got only 4 rows in the result set which are present in the first result set but not in the second result set.

In the next article, I am going to discuss UNION Operator in Oracle with Examples. Here, in this article, I try to explain SET Operators in Oracle with Examples and I hope you enjoy this SET Operators in Oracle with Examples article. If you have any queries regarding the Oracle SET Operators, then please let us know by putting your query in the comment section.