SQL EXISTS and NOT EXISTS: asking whether a related row exists

This lesson uses the tables from P07.

Many SQL questions only need to check whether a matching row exists in another table.

For example:

• Which customers have placed at least one paid order?
• Which customers have never placed an order?
• Which orders have been shipped?

These questions do not need any columns from the other table. They only need a yes or no answer for each row. We can use a join to answer them, but a join which we covered in P07 returns one row for every match, which can produce more rows than we need.

Consider the requirement: Find customers who have placed at least one paid order.

SELECT
    c.customer_name
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.customer_name;

Notice that Asha appears twice because she has two paid orders.

This is the join fan-out we explored in P07, section 07. The join returns one row for each matching paid order, even though we only want to know whether the customer has paid at least once. We could add DISTINCT to remove duplicate customer rows. That would give us the expected result, but we would still be using a join to produce matches we do not actually need.

SQL provides a more direct way to answer this question: EXISTS. EXISTS checks whether a subquery returns at least one row. We can use it to keep customers who have a matching paid order without repeating them for every match.

Two important concepts will appear throughout this lesson:

• Semi-join: Keeps rows from one table when at least one matching row exists in another table.
• Anti-join: Keeps rows from one table when no matching row exists in another table. We saw this pattern using LEFT JOIN ... IS NULL in P07, section 14.

The requirement is to find customers who have placed at least one paid order.

Instead of joining customers with their orders, we can use EXISTS to check whether each customer has a matching paid order.

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
)
ORDER BY c.customer_id;

EXISTS checks whether a subquery returns at least one row:

• TRUE: The subquery finds one or more matching rows.
• FALSE: The subquery finds no matching rows.

Notice that the subquery uses c.customer_id, which comes from the outer customers query. This is called a correlated subquery because the inner query refers to a column from the outer query.

Why SELECT 1?

Inside the subquery, we wrote:

SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
  AND o.status = 'paid'

The value 1 is not added to the outer query’s result. EXISTS only checks whether the subquery returns at least one row. It does not use the values selected by that subquery.

For this reason, all three expressions work the same way:

• SELECT 1
• SELECT *
• SELECT o.order_id

We commonly use SELECT 1 because it makes the intention clear: we care only about whether a matching row exists, not what that row contains.

The warehouse tracks shipped orders in a separate shipments table.

The requirement is to find customers who have had at least one order shipped.

One approach is to start with orders, use EXISTS to find orders that have a matching shipment, and return their customer_id. But will this give us one row per customer?

SELECT o.customer_id
FROM orders AS o
WHERE EXISTS (
    SELECT 1
    FROM shipments AS s
    WHERE s.order_id = o.order_id
)
ORDER BY o.customer_id;

Getting one row per customer

If the requirement is to return each customer only once, we should start with customers instead of orders. Then, inside EXISTS, we can check whether that customer has any order with a matching shipment.

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    INNER JOIN shipments AS s
        ON s.order_id = o.order_id
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Now the outer query reads from customers, where each row represents one customer. For each customer, the subquery checks whether at least one of their orders has a matching shipment. If a match exists, the customer is included. Even if a customer has multiple shipped orders, EXISTS only determines whether that customer qualifies.

The table in the outer query determines what each result row represents. Starting from orders gives us one row per qualifying order. Starting from customers gives us one row per qualifying customer.

IN: Another way to check for a match

We can also use IN when we want to check whether a value appears among the values returned by a subquery. For example, to find customers who have placed at least one paid order:

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.status = 'paid'
)
ORDER BY c.customer_id;

How does IN work here?

The inner query retrieves the customer_id values from all paid orders. In our data, these values are 1, 3, 1, 2 and NULL (the order of values is not important).

The outer query then checks whether each customer’s ID appears in those values.

• Asha (1): Her ID appears, so she is included.
• Ben (2): His ID appears, so he is included.
• Carla (3): Her ID appears, so she is included.
• Dev (4) and Esha (5): Neither ID appears, so they are excluded.

Notice that Asha’s ID appears twice in the subquery, but she still appears only once in the result. That is because IN checks whether a value has a match. It does not produce a separate result row for each occurrence.

The NULL from the guest order does not cause any additional customer to match. Although NULL can make an unsuccessful IN comparison evaluate to UNKNOWN rather than FALSE, both are excluded by WHERE. We will see why this distinction becomes important with NOT IN in section 07.

For this requirement, IN returns the same customers as the EXISTS query from section 02.

The requirement is the same as in section 02: customers with at least one paid order.

SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.status = 'paid'
)
ORDER BY c.customer_id;

Suppose we want to find customers who have had at least one order shipped.

One approach is to use IN to check whether a customer’s ID appears in a list returned by a subquery. However, the shipments table contains only shipment_id, order_id and shipped_date. It does not have a customer_id column.

What happens if we accidentally reference customer_id inside the subquery?

SELECT c.customer_name
FROM customers AS c
WHERE c.customer_id IN (
    SELECT customer_id
    FROM shipments
)
ORDER BY c.customer_id;

How to prevent this mistake

Always qualify column references inside subqueries using their table aliases. For example, if we wrote:

SELECT s.customer_id
FROM shipments AS s

SQL would report an error because customer_id does not exist in shipments. The alias s explicitly tells SQL which table should contain the column, preventing it from silently using the outer query’s column. To solve the original requirement correctly, we need to connect customers to shipments through orders, as demonstrated in section 03.

While EXISTS checks whether at least one matching row exists, NOT EXISTS checks whether no matching rows exist. This is called an anti-join. It keeps rows from the outer query only when the subquery finds no match.

Consider the requirement: Find customers who have never placed an order.

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

For each customer, the subquery checks whether any matching order exists.

• Asha has placed orders, so the subquery returns rows. NOT EXISTS evaluates to FALSE, and she is excluded.
• Esha has never placed an order, so the subquery returns no rows. NOT EXISTS evaluates to TRUE, and she remains.

The important detail is that the conditions inside the subquery determine what counts as a match.

Let’s see why this matters when the requirement changes.

The new requirement is to find customers with zero paid orders. This includes customers who have placed only non-paid orders and those who have never placed an order.

Someone interprets this as finding customers who have placed an order that is not paid, and writes:

SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status <> 'paid'
)
ORDER BY c.customer_id;

The correct approach: NOT EXISTS

To find customers with no paid orders, we need to check that no order with status paid exists for that customer.

SELECT c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
)
ORDER BY c.customer_id;

Now the subquery searches specifically for paid orders.

• Ben has a paid order, so NOT EXISTS is FALSE. He is excluded.
• Dev has no paid orders, so NOT EXISTS is TRUE. He is included.
• Esha has no orders at all, so she also has no paid orders. NOT EXISTS is TRUE, and she is included.

The query now matches the requirement.

Understanding the difference

These two conditions may look similar, but they ask different questions.

The first condition only needs to find one non-paid order, even if the customer also has paid orders. The second condition ensures that no paid order exists for the customer, regardless of how many other orders they may have.

In P02, sections 08 and 09, we learned that NULL can cause unexpected results with NOT IN.

If a NOT IN list contains NULL, a value that does not match any non-NULL item produces UNKNOWN, not TRUE. This becomes especially problematic when the list comes from a subquery, because we may not know whether it contains NULL values.

Consider the requirement from section 06: Find customers who have never placed an order.

We can try solving it using NOT IN:

SELECT c.customer_name
FROM customers AS c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders AS o
)
ORDER BY c.customer_id;

Result: No rows.

Why does the query return nothing when Esha has never placed an order?

The problem is order 1007, a guest order with a NULL customer_id. The subquery returns the following values:

1, 2, 3, 4, 1, 2, NULL

For Esha, whose customer_id is 5, the condition becomes:

5 NOT IN (1, 2, 3, 4, 1, 2, NULL)

This is equivalent to checking:

5 <> 1 AND
5 <> 2 AND
...
5 <> NULL

Every comparison with a non-NULL value is TRUE, but 5 <> NULL evaluates to UNKNOWN.

Combining these conditions produces UNKNOWN, and WHERE keeps only rows where the condition is TRUE. As a result, Esha is excluded.

The other customers are also excluded because their IDs appear in the list, making NOT IN evaluate to FALSE.

The query runs without errors but returns an incorrect result.

How to fix it

There are two approaches.

Option 1: Use NOT EXISTS

The NOT EXISTS query from section 06 returns Esha correctly. It checks whether a matching order exists for each customer. The guest order has a NULL customer_id, so it cannot match any customer’s ID using =. Therefore, it does not affect the result.

Option 2: Remove NULL values before using NOT IN

SELECT c.customer_name
FROM customers AS c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.customer_id IS NOT NULL
)
ORDER BY c.customer_id;

By excluding NULL values from the subquery, NOT IN now correctly identifies customers whose IDs do not appear in orders.

What if the outer value is NULL?

There is another difference worth understanding.

If the outer value is NULL, NOT IN does not return TRUE when the subquery contains values, even if the subquery itself has no NULL values.

In contrast, NOT EXISTS with an ordinary equality condition treats a NULL outer key as having no match, so the outer row is included.

This difference matters when the outer table contains nullable join keys. We will explore such a case in section 10.

In P07, section 05, we learned that when we want to filter rows from the left table of a LEFT JOIN, the condition belongs in WHERE, not ON.

A similar principle applies to EXISTS and NOT EXISTS: conditions that filter the outer rows should generally stay in the outer WHERE clause.

Consider this query, which finds customers from India who have placed at least one order:

SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND c.country = 'India'
)
ORDER BY c.customer_id;

The query returns the expected result, even though c.country = 'India' is inside the subquery.

Why?

For a customer outside India, the country condition is FALSE. The subquery returns no rows, so EXISTS evaluates to FALSE and excludes that customer. For customers in India, the subquery checks whether they have any orders. Asha and Dev both qualify. Although this works, placing an outer-row condition inside the subquery can cause confusing behaviour, especially with NOT EXISTS.

What changes with NOT EXISTS?

Now the requirement is to find customers outside India who have never placed an order.

Someone writes the country condition inside NOT EXISTS, following the same approach:

SELECT c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND c.country <> 'India'
)
ORDER BY c.customer_id;

The result is incorrect. Asha and Dev are from India, so they should not appear.

Why does this happen?

Look at the condition inside the subquery:

WHERE o.customer_id = c.customer_id
  AND c.country <> 'India'

For Asha and Dev, c.country <> 'India' is FALSE because both live in India. This means their subqueries return no rows, regardless of whether they have placed orders. But NOT EXISTS evaluates to TRUE when the subquery returns no rows.

As a result:

• Asha and Dev are incorrectly included because the country condition prevents their subqueries from returning any rows.
• Ben and Carla are excluded because they live outside India and have placed orders.
• Esha is correctly included because she lives outside India and has never ordered.

The mistake is that the country condition is being used to decide whether a matching order exists, rather than to filter customers.

The fix: Filter customers in the outer WHERE

The requirement has two separate conditions:

1. The customer must live outside India.
2. The customer must have no matching orders.

Write each condition where it belongs:

SELECT c.customer_name
FROM customers AS c
WHERE c.country <> 'India'
  AND NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
ORDER BY c.customer_id;

Now the query first restricts the result to customers outside India, then checks whether each qualifying customer has any orders. Only Esha satisfies both conditions.

So far, we have used EXISTS and NOT EXISTS to check whether at least one matching row exists. But some requirements are about every related row, not just one.

For example:

• Customers whose orders are all paid.
• Products stocked by every store.
• Accounts where every login used two-factor authentication.

SQL does not have a direct FOR ALL keyword for these questions. Instead, we can use NOT EXISTS to check that no row violates the requirement.

Consider the requirement: Find customers whose orders are all paid.

SELECT c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status <> 'paid'
)
ORDER BY c.customer_id;

What if customers must have placed an order?

Suppose the requirement is more specific:

Find customers who have placed at least one order and have paid for every order they placed.

Now Esha should not qualify because she has never ordered. We need two checks:

1. EXISTS confirms that the customer has placed at least one order.
2. NOT EXISTS confirms that none of their orders has a status other than paid.

SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
)
AND NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status IS DISTINCT FROM 'paid'
)
ORDER BY c.customer_id;

Now only Asha and Carla remain. Both have placed orders, and every order they placed is paid.

Why use IS DISTINCT FROM?

Notice that the second query uses:

o.status IS DISTINCT FROM 'paid'

instead of:

o.status <> 'paid'

The difference matters when status contains NULL.

A comparison such as NULL <> 'paid' evaluates to UNKNOWN, not TRUE. Therefore, an order with a NULL status would not satisfy the subquery’s condition, and its customer might incorrectly qualify as having only paid orders.

IS DISTINCT FROM treats NULL as different from 'paid', so an order with a missing status prevents the customer from qualifying.

Our sample data contains no NULL statuses, so both conditions produce the same result here.

For databases that do not support IS DISTINCT FROM, use:

(o.status <> 'paid' OR o.status IS NULL)

A correlated subquery can also compare a row with other rows in the same table. This makes NOT EXISTS useful for finding the latest row within each group, such as a customer’s most recent order.

The requirement is to find the most recent order placed by each customer.

We can express this as an existence check: An order is the most recent if no later order exists for the same customer.

To test this, we read orders twice using different aliases:

• o represents the order we are checking.
• later represents other orders belonging to the same customer.

SELECT
    o.order_id,
    o.customer_id,
    o.order_date
FROM orders AS o
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS later
    WHERE later.customer_id = o.customer_id
      AND later.order_date > o.order_date
)
ORDER BY o.order_id;

Handling NULL values and ties

Before using this pattern in a real query, consider three cases.

1. NULL customer IDs

Orders without a customer ID cannot be assigned to a customer. Exclude them using o.customer_id IS NOT NULL in the outer WHERE clause.

2. NULL order dates

If an order has a NULL date, comparisons such as later.order_date > o.order_date evaluate to UNKNOWN. This means the subquery cannot find an order that is later than the NULL-dated order, so that order may incorrectly remain in the result.

Exclude such rows using o.order_date IS NOT NULL.

3. Multiple orders on the same date

Suppose a customer places two orders on their most recent order date. Because the query uses later.order_date > o.order_date, neither order is considered later than the other. Both would remain. To return exactly one order per customer, we need a tie-breaker.

For this example, when two orders have the same date, the order with the larger order_id is considered the latest.

The corrected query

SELECT
    o.order_id,
    o.customer_id,
    o.order_date
FROM orders AS o
WHERE o.customer_id IS NOT NULL
  AND o.order_date IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM orders AS later
    WHERE later.customer_id = o.customer_id
      AND (
          later.order_date > o.order_date
          OR (
              later.order_date = o.order_date
              AND later.order_id > o.order_id
          )
      )
)
ORDER BY o.order_id;

The query now checks whether another order for the same customer has:

• A later order_date, or
• The same order_date but a larger order_id.

If either condition is satisfied, the current order is not the latest and is excluded. We also exclude rows with NULL customer IDs or order dates, so the result contains one latest order for each customer who has at least one order with a valid date.

Is NOT EXISTS the best approach for large tables?

NOT EXISTS expresses the requirement clearly and works across SQL engines that support correlated subqueries. However, finding the latest row this way can require comparing multiple orders within the same customer group. For customers with many orders, this may become expensive, depending on the database and available indexes.

A common alternative is the ROW_NUMBER() window function, which can rank each customer’s orders by date and order ID. We will explore this approach later in the learning path.

We previously used EXISTS to find customers who have placed at least one paid order. Now suppose someone tries to solve the same requirement by counting paid orders inside the subquery.

SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT COUNT(*)
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
)
ORDER BY c.customer_id;

How to check the number of matching rows

Suppose the requirement changes to find customers who have placed at least two paid orders.

Now we need to check the number of matching orders, not just whether a match exists. One approach is to use GROUP BY and HAVING inside an IN subquery:

SELECT c.customer_name
FROM customers AS c
WHERE c.customer_id IN (
    SELECT o.customer_id
    FROM orders AS o
    WHERE o.status = 'paid'
    GROUP BY o.customer_id
    HAVING COUNT(*) >= 2
)
ORDER BY c.customer_id;

The subquery groups paid orders by customer and keeps only customers with at least two paid orders. Only Asha meets this requirement.

Can we check the count directly inside EXISTS? Consider this query, which uses HAVING without GROUP BY:

SELECT c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'paid'
    HAVING COUNT(*) >= 2
)
ORDER BY c.customer_id;

So far, we have used EXISTS in WHERE to decide which rows should appear in a result. But EXISTS can also be used in SELECT to add a TRUE/FALSE column indicating whether a matching row exists.

This is useful when we want to display every record along with a flag, rather than exclude records without matches.

Consider the requirement: Show every customer with a flag indicating whether they have ever placed an order.

We can write this using either IN or EXISTS. Let’s compare both approaches:

SELECT
    c.customer_name,
    c.customer_id IN (
        SELECT o.customer_id
        FROM orders AS o
    ) AS ordered_in,
    EXISTS (
        SELECT 1
        FROM orders AS o
        WHERE o.customer_id = c.customer_id
    ) AS ordered_exists
FROM customers AS c
ORDER BY c.customer_id;

Both expressions return TRUE for customers who have placed orders.

However, notice what happens for Esha:

• ordered_in returns NULL.
• ordered_exists returns FALSE.

Why do they produce different values?

Why does IN return NULL?

The IN subquery retrieves all customer_id values from orders, including the NULL from guest order 1007.

Esha’s customer_id is 5, so her condition becomes:

5 IN (1, 2, 3, 4, 1, 2, NULL)

Her ID does not match any non-NULL value in the list. However, because the list contains NULL, SQL cannot determine that the comparison is definitively FALSE. The result is UNKNOWN, which appears as NULL in the output. This is the same three-valued logic we explored in P02.

Why does EXISTS return FALSE?

EXISTS works differently. It checks only whether the subquery returns any rows. For Esha, the subquery finds no matching orders, so EXISTS evaluates to FALSE. Unlike IN, EXISTS always returns either TRUE or FALSE, never NULL.

Why does this difference matter?

When IN is used in a WHERE clause, the difference between FALSE and UNKNOWN may not be visible because both cause the row to be excluded. However, the difference matters when the expression is displayed as a column or used in further calculations.

For example, suppose we want to find customers who have never ordered by negating the IN condition.

For Esha:

• ordered_in is NULL.
• NOT ordered_in is also NULL, not TRUE.

Therefore, filtering with NOT ordered_in would still exclude Esha. This can also lead to incorrect counts in reports that use the flag to identify customers without orders.

With EXISTS, the result is simpler:

• Customers with orders receive TRUE.
• Customers without orders receive FALSE.

We can safely use NOT EXISTS to identify customers who have never ordered.

Using CASE for a readable flag

We can also combine EXISTS with CASE to display a label instead of a boolean value:

SELECT
    c.customer_name,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM orders AS o
            WHERE o.customer_id = c.customer_id
        )
        THEN 'Yes'
        ELSE 'No'
    END AS ever_ordered
FROM customers AS c
ORDER BY c.customer_id;

This format is useful for reports where a descriptive value is easier to understand than TRUE or FALSE.

Database note: DuckDB and PostgreSQL display boolean values as TRUE or FALSE, while MySQL commonly represents them as 1 or 0.

Queries using EXISTS and NOT EXISTS often run without errors, even when their results are incorrect. You might see duplicate customers, missing rows, unexpected NULL values, or every customer appearing when only a few should qualify.

Use the following table to identify common mistakes:

How to check your results

When testing an existence query, include cases that reveal incorrect logic:

• A customer with no orders: Esha helps verify whether unmatched customers are handled correctly.
• A customer with multiple matching orders: Asha helps identify duplicate rows or incorrect counting.
• An order with a NULL key: Order 1007 helps reveal problems with IN, NOT IN, and correlation conditions.

These cases help catch mistakes that may not be obvious when testing only customers with a single matching order.

Before writing a query involving related tables, answer four questions.

1. Do you need columns from the other table?
If you need order details alongside customer information, a JOIN is usually appropriate. If you only need to check whether an order exists, use an existence condition instead.

2. Are you looking for a match or the absence of one?
Use EXISTS when at least one matching row must exist. Use NOT EXISTS when no matching row should exist.

3. What counts as a valid match?
Put the relationship between the tables and conditions on matching rows inside the subquery. Keep conditions that filter the outer rows in the outer WHERE clause.

4. Does the requirement involve every matching row or a specific number of matches?
For conditions such as “every order is paid,” use NOT EXISTS to check that no order violates the rule. For conditions such as “at least three paid orders,” use an aggregate with HAVING or another explicit count comparison.

Different ways to find rows without matches

Consider the requirement: Find customers who have never placed an order. We already know how to solve this using NOT EXISTS. We can also use the LEFT JOIN ... IS NULL pattern from P07:

SELECT c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Both approaches return Esha because she has no matching orders. A third approach uses NOT IN, but it requires removing NULL values from the subquery’s result, as we saw in section 07.

Here is how these approaches compare:

What about performance?

EXISTS, IN, and joins can sometimes be optimized into similar execution plans.

For example, a database may implement EXISTS using a semi-join and NOT EXISTS using an anti-join. A LEFT JOIN ... IS NULL query may also be optimized into an anti-join.

However, performance depends on the database engine, indexes, table sizes, and available statistics. NOT IN can also require different handling because of its NULL semantics.

There is no single approach that is always fastest.

Choose the query that correctly expresses the requirement first. If performance becomes a concern, inspect the execution plan and measure the query rather than assuming one syntax is faster.

EXISTS checks whether a subquery returns at least one row, while NOT EXISTS checks whether it returns none. Both let us answer questions about related data without creating additional rows for every match.

The most important part is defining what counts as a match. Keep the correlation condition correct, handle NULL values carefully, and choose the outer table based on what each result row should represent.

When the requirement involves every related row, look for a row that violates the condition. When it involves a specific number of matches, use an aggregate to test the count.

Key idea: Use existence checks when the question is whether matching data exists, not when you need the matching rows themselves.

Next, in P09, we will learn how to combine and compare entire result sets using UNION, UNION ALL, INTERSECT, and EXCEPT. These operations provide another way to answer questions such as finding customers who have never placed an order.