Azam Sajid's Blog

MINUS vs NOT EXISTS Oracle SQL

Azam Sajid's photo
Azam Sajid
·

2 min read

Table of contents

The choice between MINUS and NOT EXISTS in Oracle SQL depends on the specific scenario and performance considerations. Both can be used to find rows in one table that do not exist in another, but they work differently and can have different performance characteristics.

MINUS

MINUS returns all rows in the first query that are not in the second query. It's straightforward to use but may not always be the most efficient for large datasets.

Example:

SELECT column1, column2 FROM tablea
MINUS
SELECT column1, column2 FROM tableb;

NOT EXISTS

NOT EXISTS is a correlated subquery that checks for the non-existence of rows in a subquery. It can be more flexible and sometimes more efficient, especially with appropriate indexing.

Example:

SELECT column1, column2 
FROM tablea a
WHERE NOT EXISTS (
    SELECT 1 
    FROM tableb b 
    WHERE a.column1 = b.column1 
      AND a.column2 = b.column2
);

When to Use Each

  • Use MINUS:

    • When you need a straightforward way to find differences between two sets.

    • For simpler queries where performance is not a critical issue.

  • Use NOT EXISTS:

    • When you need more flexibility in your conditions.

    • When dealing with larger datasets and proper indexing is in place.

    • When the subquery is expected to be selective, i.e., returns fewer rows.

Performance Considerations

  • MINUS can sometimes be less efficient for large datasets as it involves sorting operations.

  • NOT EXISTS can be more efficient with proper indexing, especially when the subquery is selective.

  • It's often beneficial to test both approaches with your specific data and query to see which performs better.

Example Comparison

Using MINUS:

SELECT id, name FROM employees
MINUS
SELECT id, name FROM contractors;

Using NOT EXISTS:

SELECT e.id, e.name 
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 
    FROM contractors c 
    WHERE e.id = c.id 
      AND e.name = c.name
);

In summary, both MINUS and NOT EXISTS can be used to achieve similar results, but NOT EXISTS often provides better performance and flexibility for more complex queries or larger datasets.

OracleSQLdataanalyticsdatascience