BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 35 of 1,9531. Relational FoundationsFree lesson

Existential Checks

Use semi-joins to verify the existence of related data without fetching the full payload.

In this lesson, you will learn how to efficiently verify the existence of related data using semi-joins, allowing your database to confirm a relationship without the overhead of retrieving extra information.

The Problem with Full Joins

When you need to find rows in one table that have a match in another, you might be tempted to use a standard INNER JOIN. For example, if you want to find all customers who have placed at least one order, you could join Customers to Orders.

However, if a customer has placed 50 orders, a standard join will return that customer’s name 50 times in your result set. You then have to use DISTINCT to clean up the duplicates. This is computationally expensive and conceptually messy.

The Semi-Join Approach: EXISTS

A semi-join, performed using the EXISTS keyword, acts like a filter. It asks the database a simple "Yes/No" question: "Does at least one matching row exist in the related table?"

Because the database stops looking as soon as it finds the first match for each row, it doesn't waste time scanning the entire Orders table or assembling a massive result set.

SELECT c.customer_name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

Why use EXISTS?

  1. Efficiency: The database engine short-circuits. Once it confirms that a match exists, it moves to the next customer immediately.
  2. Clarity: It clearly signals your intent. You aren't asking for the order data; you are only interested in the existence of the order.
  3. No Duplication: Because this is a filter and not a projection, you don't need to worry about DISTINCT to remove duplicate rows caused by multiple orders.

Think of an INNER JOIN as "give me the rows and their details," whereas an EXISTS check is simply "tell me if this connection exists." By using semi-joins, you keep your queries focused on the specific logic of the relationship rather than the volume of the data involved.

Key Takeaway: Use EXISTS for semi-joins to verify relationships without retrieving extra data or creating duplicate rows.