Evaluating Existence with Semi-Joins

Evaluating Existence with Semi-Joins

In this lesson, you will learn how to verify that related data exists in another table without accidentally duplicating your result rows.

The Problem: The Duplicate Trap

When you want to find customers who have placed at least one order, you might be tempted to use a standard JOIN. However, if a customer has placed five orders, a standard JOIN will return that customer's name five times—one for each order. If you only want to know if they have an order (a simple "yes" or "no" check), this duplication creates extra work for you to clean up.

The Solution: The Semi-Join

A semi-join is a specific way of filtering your primary dataset based on the presence of records in a secondary dataset. Instead of merging the data columns together, the database simply checks: "Does a match exist for this record?" If the answer is yes, the record from your main table is kept; if not, it is discarded.

In SQL, we typically implement this using the EXISTS operator combined with a correlated subquery.

Putting it into Code

Imagine we have a customers table and an orders table. We want to list all customers who have placed at least one order:

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

Why This Works

1. No Duplication: Because EXISTS is a filter (in the WHERE clause) rather than a JOIN operation, it does not multiply your rows. If a customer has 100 orders, they still only appear once in your output.
2. Efficiency: The database engine is smart; as soon as it finds one matching order for a customer, it stops looking through the rest of the orders for that person and moves to the next customer. It doesn't need to count them or retrieve the order details—it only cares about existence.

Using EXISTS is the standard way to answer "do these records exist?" while keeping your result set clean and concise.

Key Takeaway: A semi-join, typically implemented via EXISTS, allows you to filter your main dataset by confirming the presence of related records without creating duplicate rows in your output.