Anti-semi Join

Overview

An Anti-semi Join (or Anti Join) returns rows from the left table that have no match in the right table.

Learning Objectives

• Understand Anti-semi Join execution logic.
• Implement Anti Joins using NOT EXISTS or LEFT JOIN ... WHERE right.id IS NULL.
• Filter out records present in secondary datasets efficiently.

Detailed Concept Explanation

Anti-semi Joins complement Semi Joins. Instead of keeping rows that match, an Anti-semi Join keeps rows from the left table that have zero matching records in the right table.

In SQL, Anti Joins are typically written using:

1. NOT EXISTS
2. LEFT JOIN ... WHERE right_table.key IS NULL

Code Examples

SQL

-- Anti Join using NOT EXISTS (Recommended)
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Anti Join using LEFT JOIN
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

Best Practices

• Use NOT EXISTS: NOT EXISTS is generally cleaner and less error-prone for Anti Joins than NOT IN (which breaks when NULL values are present).

Interview Perspective

Interactive Challenges

Summary

Anti-semi Joins return records from a primary table that have no matching entries in a secondary table.