Select Statement  «Prev  Next»

Lesson 4 UNION, INTERSECT, and MINUS
Objective Compare the INTERSECT, MINUS, and UNION commands.

UNION, INTERSECT, and MINUS SQL Operations

Question: How do I combine the results of two separate queries using the UNION command in Oracle?
To combine the results of two separate queries using the UNION command in Oracle, you need to follow these steps:
  1. Write the first query, making sure to select the desired columns. The number of columns, their data types, and their order should be the same in both queries.
  2. Use the 'UNION' keyword to combine the results of the two queries. This keyword will only return distinct rows from both queries (i.e., it will remove duplicates).
  3. Write the second query, making sure to select the same number of columns, with the same data types and in the same order as the first query.

Here's an example:
Assuming you have two tables 'employees' and 'contractors' with the same columns: 'id', 'first_name', 'last_name', and 'email', and you want to create a list of all employees and contractors with their full names and email addresses, you would write the following query:
SELECT first_name || ' ' || last_name AS full_name, email
FROM employees
UNION
SELECT first_name || ' ' || last_name AS full_name, email
FROM contractors;

In this example, the '||' operator is used to concatenate the first_name and last_name columns to create a full_name column.
The 'UNION' keyword combines the results of the two SELECT statements, and it will only return distinct rows. If you want to include duplicate rows in the result, you can use 'UNION ALL' instead:
SELECT first_name || ' ' || last_name AS full_name, email
FROM employees
UNION ALL
SELECT first_name || ' ' || last_name AS full_name, email
FROM contractors;

Remember to make sure the number of columns, their data types, and their order are the same in both queries for the UNION command to work correctly.
How do you combine the results of two separate queries? Oracle provides three commands,
  1. UNION,
  2. INTERSECT and
  3. MINUS,
that handle combining query results in three different ways.
Command Description
UNION The most commonly used command, UNION combines the two answer sets into a single answer set. It automatically removes duplicate rows from the results.
INTERSECT INTERSECT gives you the rows that are found in both queries by eliminating rows that are only found in one or the other query.
MINUS MINUS gives you the rows that are found in the first query and not in the second query by removing from the results all the rows that are found only in the second query.

UNION, INTERSECT, and MINUS SQL Operations

Differences between commands

The following animation shows you the difference between these three commands by using two circles to represent two query result sets, labeled A and B. The animation shows which portions of the two result sets are returned by combining the two queries with each of the three set commands: INTERSECT, then UNION, then MINUS.

INTERSECT, then UNION, then MINUS commands
INTERSECT, then UNION, then MINUS commands

Command rules

There are two important rules to follow when using these commands:
  1. Both queries must have matching lists of columns with matching datatypes. In other words, if your first query returns three columns, a date, a number, and a character column, your second query must also return three columns: a date, a number, and a character column, in that order. The two queries do not need to use the same column names.
  2. The first query's list of columns is used for the final results set. If two corresponding columns in the two queries have different lengths, the first query is used to set the length of the final results set. If the second query's column is longer, you may get an error in your results due to data truncation.

Here is an example of two queries connected with the INTERSECT command:

SELECT TO_CHAR(LAST_UPDATE_DATE,'YYYY-MM'), 
 UPDATED_BY_USER
FROM PRODUCT
INTERSECT
SELECT TO_CHAR(LAST_UPDATE_DATETIME,'YYYY-MM'), 
 CREATED_BY_USER
FROM PET_CARE_LOG
ORDER BY 1

This query finds the dates (year and month only) and the employee names that are the same for changes made to the PRODUCT table and log entries made to the PET_CARE_LOG.
In the next lesson, you will learn how to use advanced set operators.

Intersect union minus Operations

Click the link below to read about the intersect, union, minus operations used in Oracle.
Intersect, union, minus Operations