UNION, INTERSECT, and MINUS SQL Operations

In Oracle, the `UNION` command is used to combine the results of two separate `SELECT` queries into a single result set while ensuring distinct rows (i.e., it removes duplicates). If you want to include duplicate rows, you can use `UNION ALL` instead.
Syntax:

SELECT column1, column2, column3
FROM table1
WHERE condition1
UNION
SELECT column1, column2, column3
FROM table2
WHERE condition2;

Key Rules:

• The number of columns and their data types must match in both SELECT queries.
• UNION removes duplicates, while UNION ALL keeps all records, including duplicates.

Example: Using `UNION` to Combine Two Queries

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10
UNION
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 20;

Explanation:

• This query retrieves distinct employees from department 10 and department 20.
• If an employee exists in both departments, UNION ensures they appear only once.

Example: Using `UNION ALL` to Include Duplicates

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10
UNION ALL
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 20;

Difference: - Unlike `UNION`, `UNION ALL` does not remove duplicates.
Sorting Results Using `ORDER BY`
If you want to order the final result, apply `ORDER BY` to the entire result set at the end:

SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 10
UNION
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = 20
ORDER BY department_id, last_name;

This will return all distinct employees from departments 10 and 20, sorted by department_id and last_name.
Combine the results of two Separate Queries Question: 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.

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.

There are two important rules to follow when using these commands:

1. Rule 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. Rule 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.
The following paragraphs below describe the intersect, union, and minus operations in greater detail.

The correct commands for the scenarios are:

1. INTERSECT: Find out which employee has updated at least one record in the PRODUCT table and added at least one record in the PET_CARE_LOG table.
2. UNION: Find out which customers have purchased a dog in one sale and have purchased a cat in another sale.
3. MINUS: Determine which products purchased by Amy Black have not been purchased by Lester Lee.

• UNION, INTERSECT, and MINUS
  Sometimes you need to combine information of a similar type from more than one table. A classic example of this is merging two or more mailing lists prior to a mailing campaign. Depending on the purpose of a particular mailing, you might want to send letters to any of these combinations of people: Everyone in both lists (while avoiding sending two letters to someone who happens to be in both lists)
  1. Only those people who are in both lists
  2. Those people in only one of the lists
  These three combinations of lists are known in Oracle as UNION, INTERSECT, and MINUS. In the following examples, you will see how to use these three clauses to manage the results of multiple queries. The examples will compare the books on hand (BOOKSHELF) with those on order (BOOK_ORDER). To see all the books, UNION the two tables. To reduce the size of the output, only the BOOKSHELF entries from the first half of the alphabet are selected.

The following select returns 14 rows:

select Title from BOOKSHELF
where Title < 'M%';

And this select returns six rows:

select Title from BOOK_ORDER;

If we UNION them together, how many rows are returned?

select Title from BOOKSHELF
where Title < 'M%'
union
select Title from BOOK_ORDER;
TITLE

In the next lesson, you will learn how to use advanced set operators.