What Is Oracle MINUS operator?

The MINUS operator in Oracle SQL is used to return the result of one query’s results minus the results of another query. In other words, it returns all rows that appear in the first query but not in the second query. It is typically used to subtract one dataset from another.

Syntax:

SELECT column1, column2, ...
FROM table1
MINUS
SELECT column1, column2, ...
FROM table2;

• The MINUS operator works on entire result sets, comparing each row from the first query with the rows from the second query.
• It will remove duplicates and only return distinct rows that exist in the first query but not in the second.

Key Points:

1. Column Compatibility:
   The two SELECT statements must have the same number of columns, and the corresponding columns must be of compatible data types. If they are not compatible, an error will be raised.
2. Duplicate Elimination:
   Like DISTINCT, the MINUS operator automatically removes duplicate rows from the result set. Only unique rows that appear in the first query and not in the second are returned.
3. ORDER BY Clause:
   You can use the ORDER BY clause to order the result set, but it must be placed after theMINUS operator and should refer to the column numbers rather than the column names.

Example:

1. Basic Example

Let’s say we have two tables: employees_2023 and employees_2024. Both tables have the same structure, containing employee data for different years.

-- employees_2023 table
SELECT employee_id, employee_name FROM employees_2023;

-- employees_2024 table
SELECT employee_id, employee_name FROM employees_2024;

Now, we want to find out which employees were in the employees_2023 table but are not in the employees_2024 table. This can be done with the MINUS operator:

SELECT employee_id, employee_name
FROM employees_2023
MINUS
SELECT employee_id, employee_name
FROM employees_2024;

Explanation:

• The query above will return all employees that were present in employees_2023 but not in employees_2024.
• Any employee that exists in both tables will not appear in the result.

2. Example with Duplicates:

Assume you have the following data in two tables:

employees_2023:

employees_2024:

If you run the following query:

SELECT employee_id, employee_name
FROM employees_2023
MINUS
SELECT employee_id, employee_name
FROM employees_2024;

Output:

• Explanation:
  The employee John Doe appears in both tables, so he is eliminated from the result, even though there is a duplicate of John Doe in employees_2023. The only employee returned is Jane Smith because she exists in employees_2023 but not in employees_2024.

3. Using ORDER BY with MINUS:

If you want to sort the result, you can use ORDER BY at the end of the query:

SELECT employee_id, employee_name
FROM employees_2023
MINUS
SELECT employee_id, employee_name
FROM employees_2024
ORDER BY employee_id;

This will return the rows that are in employees_2023 but not in employees_2024, sorted by employee_id.

4. Combining MINUS with Other Operators (UNION, INTERSECT):

You can combine the MINUS operator with other set operators like UNION and INTERSECT to perform more complex queries.

Example: Combining UNION and MINUS:

-- Find employees who are in either employees_2023 or employees_2024, but not in both
SELECT employee_id, employee_name
FROM employees_2023
UNION
SELECT employee_id, employee_name
FROM employees_2024
MINUS
SELECT employee_id, employee_name
FROM employees_2023
INTERSECT
SELECT employee_id, employee_name
FROM employees_2024;

This query first combines employees from both tables using UNION, then removes those who appear in both using MINUS, and finally finds the intersection using INTERSECT.

Important Notes:

1. Performance Considerations:
   Since MINUS eliminates duplicates and compares entire result sets, it can be resource-intensive for large datasets. If performance is critical, ensure your tables are indexed appropriately, especially on the columns used in the SELECT clause.
2. Alternatives to MINUS:
   Depending on your use case, you may also be able to use a LEFT JOIN or NOT EXISTS with subqueries to achieve similar results, but MINUS provides a more straightforward syntax for subtracting datasets.

Example using LEFT JOIN:

SELECT e1.employee_id, e1.employee_name
FROM employees_2023 e1
LEFT JOIN employees_2024 e2
ON e1.employee_id = e2.employee_id
WHERE e2.employee_id IS NULL;

This query achieves the same result as the MINUS example by joining employees_2023 with employees_2024 and filtering out the employees that exist in both.

Conclusion:

The MINUS operator in Oracle SQL is a powerful tool for subtracting one result set from another. It helps to retrieve distinct rows from the first query that are not present in the second query. It’s essential to understand the syntax and how it eliminates duplicates and compares entire rows, which makes it suitable for various use cases, such as finding differences between datasets.