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 EXISTShandles 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 INcan 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 ANTIMERGE JOIN ANTINESTED 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:
- NOT EXISTS – Best choice in most cases.
- LEFT JOIN ... IS NULL – Also widely used and easy to read.
- 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.