MINUS FAQS

1. What is the purpose of the MINUS operator in Oracle SQL?

The MINUS operator is used to return the difference between two result sets. It returns all rows from the first SELECT statement that do not exist in the second SELECT statement. It essentially removes the rows found in the second result set from the first result set.

2. How is MINUS different from UNION?

• MINUS: Returns rows that are in the first query but not in the second. It eliminates duplicates by default.
• UNION: Combines two result sets and removes duplicates. It returns rows that are in either the first or the second query but without duplicates.

3. Does MINUS remove duplicates?

Yes, MINUS removes duplicates by default. If a row appears multiple times in the first SELECT statement but not in the second, only one occurrence of that row will appear in the result set.

4. Can MINUS be used with different numbers of columns in the SELECT statements?

No, both SELECT statements involved in a MINUS operation must return the same number of columns. If the number of columns differs, an error will occur.

5. Can the data types of the columns in the SELECT statements be different when using MINUS?

The columns in the two SELECT statements must have compatible data types. If the data types are not compatible, Oracle will throw an error. For example, you cannot subtract a VARCHAR column from a NUMBER column.

6. Does MINUS guarantee the order of the result set?

No, MINUS does not guarantee the order of the result set. If you need the results to be ordered, you should use an ORDER BY clause at the end of the query.

7. How does MINUS compare with NOT EXISTS?

Both MINUS and NOT EXISTS can be used to find rows in one query that do not exist in another:

• MINUS works by comparing two result sets and returning the difference.
• NOT EXISTS is a subquery method used in the outer query to exclude rows where the subquery condition is met.

For example, you can rewrite a MINUS query using NOT EXISTS:

SELECT employee_id FROM employees e

WHERE NOT EXISTS (

    SELECT 1 FROM attendance a WHERE a.employee_id = e.employee_id

);

8. Can I use MINUS to compare two tables with different structures?

No, the tables must have the same number of columns and compatible data types for a MINUS operation to work. You cannot compare two tables with completely different structures using MINUS unless you align their columns and ensure compatible data types.

9. Can MINUS be used with subqueries?

Yes, MINUS can be used with subqueries. You can use it to compare the results of two subqueries. Here’s an example:

SELECT column1 FROM (SELECT column1 FROM table1 WHERE condition1)

MINUS

SELECT column1 FROM (SELECT column1 FROM table2 WHERE condition2);

10. Can I use MINUS with aggregate functions?

Yes, you can use MINUS with aggregate functions as long as both SELECT statements return the same number of columns and the columns have compatible data types. For example:

SELECT department, COUNT(*) FROM employees GROUP BY department

MINUS

SELECT department, COUNT(*) FROM contractors GROUP BY department;

This query finds the difference in the number of employees per department between employees and contractors.

11. What happens if I try to subtract identical rows with MINUS?

If you subtract identical rows (i.e., the same row appears in both result sets), the row will not appear in the final result. MINUS eliminates the rows from the first SELECT that are found in the second SELECT, so if a row appears in both queries, it will not be included in the result.

12. Can I use MINUS with ORDER BY?

Yes, you can use an ORDER BY clause with MINUS to sort the final result set. However, the ORDER BY must be placed at the end of the entire query:

SELECT column1 FROM table1

MINUS

SELECT column1 FROM table2

ORDER BY column1;

13. What is the performance impact of using MINUS?

Since MINUS eliminates duplicates by default, it can be slower than operations that do not involve duplicate removal, like UNION ALL. The performance may also depend on the size of the datasets and whether indexes are used on the relevant columns.

14. Can I use MINUS to find missing data between two tables?

Yes, MINUS is often used to find records in one table that are not in another, which is useful for identifying missing or unmatched data between two datasets.

Example:

SELECT employee_id FROM table1

MINUS

SELECT employee_id FROM table2;

This will return all employee IDs that exist in table1 but not in table2.

15. Is there a way to do the reverse of MINUS (i.e., find records in the second query but not the first)?

Yes, you can achieve this by reversing the order of the queries:

SELECT employee_id FROM table2

MINUS

SELECT employee_id FROM table1;

This will return all employee IDs that exist in table2 but not in table1.