1. Overview

An anti-join in Oracle is used to find rows in one table that do not have matching rows in another table. It is essentially the opposite of a semi-join. Anti-joins are often implemented using the NOT EXISTS, NOT IN, or LEFT JOIN ... IS NULL constructs.

Anti-joins are particularly useful for identifying unmatched records, such as finding employees without assigned departments, customers without orders, or products not sold.

2. Characteristics of an Anti-Join

• Identifying Non-Matches: Returns rows from the first table that do not have a corresponding match in the second table.
• Efficient Querying: Optimized for scenarios where filtering out matching rows is required.
• Flexible Syntax: Can be implemented using multiple constructs like NOT EXISTS, NOT IN, or LEFT JOIN ... IS NULL.

3. Syntax

3.1 Using NOT EXISTS

SELECT column_list
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t1.column_name = t2.column_name
);

3.2 Using NOT IN

SELECT column_list
FROM table1 t1
WHERE t1.column_name NOT IN (
    SELECT t2.column_name
    FROM table2 t2
);

3.3 Using LEFT JOIN ... IS NULL

SELECT t1.column_list
FROM table1 t1
LEFT JOIN table2 t2
ON t1.column_name = t2.column_name
WHERE t2.column_name IS NULL;

4. Examples

4.1 Example 1: Anti-Join Using NOT EXISTS

Tables

Employee Table:

+-------------+---------+---------------+
| EMPLOYEE_ID |   NAME  | DEPARTMENT_ID |
+-------------+---------+---------------+
| 1           | Alice   | 101           |
+-------------+---------+---------------+
| 2           | Bob     | 102           |
+-------------+---------+---------------+
| 3           | Charlie | NULL          |
+-------------+---------+---------------+
| 4           | David   | NULL          |
+-------------+---------+---------------+

Department table:

+---------------+-----------------+
| DEPARTMENT_ID | DEPARTMENT_NAME |
+---------------+-----------------+
| 101           | HR              |
+---------------+-----------------+
| 102           | IT              |
+---------------+-----------------+

Query

SELECT e.employee_id, e.name
FROM employee e
WHERE NOT EXISTS (
    SELECT 1
    FROM department d
    WHERE e.department_id = d.department_id
);

Output

+-------------+---------+
| EMPLOYEE_ID |   NAME  |
+-------------+---------+
| 3           | Charlie |
+-------------+---------+
| 4           | David   |
+-------------+---------+

4.2 Example 2: Anti-Join Using NOT IN

Find employees who are not assigned to a department:

SELECT e.employee_id, e.name
FROM employee e
WHERE e.department_id NOT IN (
    SELECT d.department_id
    FROM department d
);

Note: When using NOT IN, ensure the column in the subquery (department_id in this case) does not contain NULL values. If NULL exists, the query will return no results.

4.3 Anti-Join Using LEFT JOIN ... IS NULL

Find employees who are not assigned to a department:

SELECT e.employee_id, e.name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

5. Anti-Join vs Semi-Join

Feature	Anti-Join	Semi-Join
Rows Returned	Rows in the first table with no matches.	Rows in the first table with at least one match.
Duplicates	Does not include duplicates from the second table.	No duplicates in the output.
Use Case	Identifying non-matches.	Identifying matches.

6. Performance Considerations

• Indexing: Ensure that columns in the WHERE or ON clause are indexed to optimize performance.
• Avoiding NOT IN with NULLs: Use NOT EXISTS or LEFT JOIN ... IS NULL if the column in the subquery might contain NULL values.
• Query Plans: Use EXPLAIN PLAN to analyze and tune query execution.

7. When to Use Anti-Joins

• Data Validation: Finding missing or unmatched records.
• Filtering Data: Eliminating rows based on the absence of relationships.
• Improving Performance: Anti-joins can be more efficient than alternative methods for identifying non-matching rows.