Oracle Anti Join FAQs with Examples

1. What is an Anti Join in Oracle?

An Anti Join returns rows from one table that do not have matching rows in another table.

Unlike an inner join, which returns matching records, an Anti Join returns non-matching records.

Oracle does not have an ANTI JOIN keyword. It is implemented using:

• NOT EXISTS (recommended)
• NOT IN (with care around NULL values)
• LEFT OUTER JOIN ... IS NULL
• The optimizer may internally transform these into an anti join execution plan.

2. Why do we use an Anti Join?

An Anti Join is used to:

• Find employees without departments.
• Find customers without orders.
• Find products never sold.
• Find students not enrolled in courses.
• Detect missing relationships between tables.

3. What is the syntax of an Anti Join?

Using NOT EXISTS (recommended):

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

4. How does an Anti Join work?

Suppose we have:

EMPLOYEES

EMPLOYEE_ID   EMPLOYEE_NAME   DEPARTMENT_ID
101           John            10
102           David           20
103           Scott           40

DEPARTMENTS

DEPARTMENT_ID   DEPARTMENT_NAME
10              Sales
20              HR
30              Finance

Query

SELECT employee_name
FROM employees e
WHERE NOT EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

Output

Employee
Scott

Scott belongs to department 40, which does not exist.

5. Why is NOT EXISTS preferred for Anti Joins?

NOT EXISTS:

• Handles NULL values correctly.
• Is usually easier for the optimizer to transform into an efficient anti join.
• Is the preferred method in most Oracle applications.

Example:

SELECT employee_name
FROM employees e
WHERE NOT EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

6. Can an Anti Join be written using LEFT JOIN?

Yes.

SELECT e.employee_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

This returns employees without matching departments.

7. Can an Anti Join be written using NOT IN?

Yes.

SELECT employee_name
FROM employees
WHERE department_id NOT IN
(
  SELECT department_id
  FROM departments
);

Important: If the subquery returns a NULL, NOT IN may return no rows because comparisons with NULL evaluate to unknown.

Safer version:

SELECT employee_name
FROM employees
WHERE department_id NOT IN
(
  SELECT department_id
  FROM departments
  WHERE department_id IS NOT NULL
);

8. What is the difference between NOT EXISTS and NOT IN?

NOT EXISTS	NOT IN
Safely handles NULL values	Can produce unexpected results if the subquery returns NULL
Usually preferred	Requires extra care with NULL values
Often optimized efficiently	May behave differently depending on data

9. Can an Anti Join use aliases?

Yes.

SELECT e.employee_name
FROM employees e
WHERE NOT EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

10. Can an Anti Join use a WHERE clause?

Yes.

SELECT e.employee_name
FROM employees e
WHERE e.salary > 5000
AND NOT EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

11. Can an Anti Join use multiple conditions?

Yes.

SELECT *
FROM orders o
WHERE NOT EXISTS
(
  SELECT 1
  FROM shipments s
  WHERE s.order_id = o.order_id
  AND s.customer_id = o.customer_id
);

12. Can an Anti Join use multiple tables?

Yes.

SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS
(
  SELECT 1
  FROM orders o
  JOIN payments p
  ON o.order_id = p.order_id
  WHERE o.customer_id = c.customer_id
);

13. Can an Anti Join return duplicate rows?

Yes. If the driving table contains duplicate rows, those duplicates are returned because the anti join filters rows—it does not automatically remove duplicates.

Example:

SELECT DISTINCT employee_name
FROM employees e
WHERE NOT EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

Use DISTINCT only if duplicate elimination is required.

14. What are common uses of an Anti Join?

• Customers without orders.
• Employees without departments.
• Departments without employees.
• Products without sales.
• Students without enrollments.
• Suppliers without purchase orders.
• Orders not yet shipped.

15. Real-Time Example – Customers Without Orders

CUSTOMERS

CUSTOMER_ID   CUSTOMER_NAME
1             Alice
2             Bob
3             Charlie

ORDERS

ORDER_ID   CUSTOMER_ID
100        1
101        2

Query

SELECT customer_name
FROM customers c
WHERE NOT EXISTS
(
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Output

Customer
Charlie

16. What are the advantages of an Anti Join?

• Finds missing records efficiently.
• NOT EXISTS handles NULL values correctly.
• Commonly optimized by Oracle.
• Useful for data validation and reporting.
• Easy to understand and maintain.

17. What are the disadvantages of an Anti Join?

• NOT IN can produce incorrect results when NULL values are present.
• Performance depends on indexing and data volume.
• Complex correlated subqueries can be harder to read.

18. Common Errors with Anti Joins

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00933	SQL command not properly ended
Logical errors	Using NOT IN with a subquery that returns NULL values

19. Anti Join vs Semi Join

Anti Join	Semi Join
Returns rows without a match	Returns rows with a match
Usually uses NOT EXISTS	Usually uses EXISTS
Finds missing relationships	Finds existing relationships

Semi Join Example

SELECT employee_name
FROM employees e
WHERE EXISTS
(
  SELECT 1
  FROM departments d
  WHERE d.department_id = e.department_id
);

20. Anti Join vs LEFT JOIN

Anti Join	LEFT JOIN
Returns only unmatched rows	Returns matched and unmatched rows
Often implemented with NOT EXISTS	Uses LEFT OUTER JOIN

Equivalent Anti Join using LEFT JOIN:

SELECT e.employee_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

21. Anti Join vs MINUS

Both can be used to find differences between datasets.

Using MINUS:

SELECT department_id
FROM employees

MINUS

SELECT department_id
FROM departments;

Anti Join	MINUS
Compares related rows	Compares result sets
Uses joins or subqueries	Uses set operations
More flexible	Simpler for set differences

22. How does Oracle execute an Anti Join?

Although SQL does not have an ANTI JOIN keyword, Oracle's optimizer may internally use execution plans such as:

• HASH JOIN ANTI
• MERGE JOIN ANTI
• NESTED LOOPS ANTI

The chosen plan depends on factors such as table size, indexes, statistics, and optimizer decisions.

23. Interview Scenario

Question: Display customers who have never placed an order.

Answer: Use NOT EXISTS.

SELECT customer_name
FROM customers c
WHERE NOT EXISTS
(
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

24. Which method is best for implementing an Anti Join?

General recommendation:

1. NOT EXISTS – Best choice in most cases.
2. LEFT JOIN ... IS NULL – Also widely used and easy to read.
3. NOT IN – Use only when you are certain the subquery cannot return NULL values.

---

Anti Join is a practical way to find missing relationships, and NOT EXISTS is usually the safest and clearest choice in Oracle.