Understanding Semi Joins and Anti Joins in SQL

INNER JOIN is often used to find matching rows, but it can create duplicate results if the matching table has multiple records. Semi-joins and anti-joins are conceptual patterns for existence checks, often implemented with EXISTS or NOT EXISTS.

The Problem: INNER JOIN Duplication

You need a list of customers who have placed at least one order over $100. A simple INNER JOIN would return each qualifying order, duplicating customer data.

-- ❌ Duplicates customer data
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;

If a customer has 5 orders over $100, they appear 5 times. For a customer list, this is incorrect.

Solution 1: Semi-Join with EXISTS

A semi-join returns each customer only once, regardless of how many qualifying orders they have. It "semi-joins" the customer table with the orders table.

-- ✅ Clean approach with EXISTS
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 100
);

Solution 2: Anti-Join with NOT EXISTS

An anti-join finds customers who have no orders over $100. It's the complement of the semi-join.

-- ✅ Clean approach with NOT EXISTS
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 100
);

Alternative: Using IN and NOT IN

The IN operator can also implement semi-joins and anti-joins. However, EXISTS is often more performant and handles NULL values more safely.

-- Using IN (semi-join)
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE amount > 100
);

-- Using NOT IN (anti-join) - BE CAREFUL WITH NULLS!
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE amount > 100
);

EXISTS vs. IN: Performance Comparison

Real-World Scenarios

1. Finding Inactive Users

SELECT u.id, u.username, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM logins l
    WHERE l.user_id = u.id
      AND l.login_date > DATE_SUB(NOW(), INTERVAL 30 DAY)
);

2. Finding Products Never Ordered

SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

3. High-Value Customers with Multiple Orders

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 1000
    HAVING COUNT(*) > 3
);

Best Practices

Conclusion