Optimizing Existence Checks with `EXISTS` and `NOT EXISTS`

The Foundation: Correlated Subqueries

The IN subqueries we used before were typically non-correlated (or independent). The database could run the inner query once, get a list of values, and then use that static list to filter the outer query.

EXISTS works differently. It almost always uses a correlated subquery. This is a subquery that depends on the outer query for its values and is executed repeatedly, once for each row processed by the outer query.

Given your interest in low-level systems and how things work "under the hood," understanding this distinction is key.

• Non-correlated subquery: The inner query runs once. It's like preparing a complete shopping list before you enter the store.
• Correlated subquery: The inner query runs for every row of the outer query. It's like checking your recipe for each item you pick up in the store to see if it's the right one.

This execution model is central to how EXISTS functions. For each row in the outer table, the subquery is executed with a value from that specific outer row to check if a "match" exists in the inner table.

Finding Matching Records with EXISTS

The EXISTS operator returns TRUE if its subquery returns one or more rows, and FALSE if it returns zero rows.

The syntax is:

SELECT columns
FROM table1
WHERE EXISTS (SELECT 1 
              FROM table2 
              WHERE table2.common_column = table1.common_column);

Notice a few key things:

1. The Correlation: The WHERE clause inside the subquery links table2 back to table1. This is the "correlation."
2. SELECT 1: Since EXISTS only cares about the existence of rows, not their content, it's a common convention to use SELECT 1. This is a micro-optimization; the database doesn't need to waste time retrieving actual data values. SELECT * would also work, but SELECT 1 clearly signals the query's intent.

Let's apply this to a familiar problem: finding all orders made by customers in Germany.

As shown in the video, the query checks, for each order, "Does a customer exist in the customers table with this order's customer_id AND a country of 'Germany'?" If the subquery returns a row (i.e., the answer is yes), EXISTS evaluates to TRUE and the order is included in the results.

To solidify your understanding of the row-by-row execution flow, this final clip provides a detailed visual walkthrough.

Finding Unmatched Records with NOT EXISTS

NOT EXISTS is the logical opposite. It returns TRUE if its subquery returns zero rows. This makes it the perfect tool for finding records in one table that have no corresponding records in another.

Crucially, NOT EXISTS solves the NULL problem we encountered with NOT IN. Since NOT EXISTS is checking for the existence of rows rather than comparing values, NULLs in the data don't cause the logic to break down.

To summarize the article's point:

• col NOT IN (1, 2, NULL) becomes col <> 1 AND col <> 2 AND col <> NULL.
• Since col <> NULL is always UNKNOWN, the entire expression is UNKNOWN, and the row is excluded.
• NOT EXISTS simply checks if the subquery returns any rows. A subquery looking for a NULL key might correctly return zero rows, leading NOT EXISTS to evaluate to TRUE, as intended.

Here is a practical example of finding customers who have never placed an order.

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

For each customer, this query asks: "Are there zero rows in the Orders table for this customer?" If so, NOT EXISTS is TRUE, and the customer is included.

Efficiency: EXISTS vs. IN and JOIN

A key part of the learning outcome is understanding why EXISTS is described as "efficient." The answer lies in its ability to short-circuit.

When you use EXISTS, the database can stop searching the inner table as soon as it finds the first matching row. It doesn't need to find all of them.

This short-circuiting behavior makes EXISTS a highly efficient choice for existence checks, especially on large datasets. While modern query optimizers have become very smart and can often generate similar execution plans for EXISTS and IN, EXISTS more clearly states your intent and, as we've seen, is much safer when NULLs are involved.

SELECT product_name
FROM products p
WHERE NOT EXISTS (SELECT 1 
                  FROM sales s
                  WHERE s.product_id = p.product_id);

Conclusion

You've now added one of the most powerful and important tools to your SQL arsenal. Understanding correlated subqueries and the EXISTS operator is a significant step toward writing advanced, correct, and efficient queries.

Key Takeaways:

• EXISTS and NOT EXISTS perform existence checks using correlated subqueries, which execute once for each row of the outer query.
• They return a boolean (TRUE/FALSE) based on whether the subquery returns any rows, not on the values in those rows. Using SELECT 1 is a clear and efficient convention.
• NOT EXISTS is a safer and more robust alternative to NOT IN because it is not affected by NULL values in the subquery's source data.
• EXISTS is often more efficient than JOIN or IN for simple existence checks because it can short-circuit—stopping the subquery scan as soon as the first matching row is found.

Preview of the Next Lesson:
We've now seen correlated subqueries in action within the WHERE clause. But their utility doesn't end there. In our next lesson, we will explore how a correlated subquery can be placed directly in the SELECT clause to create a calculated column on the fly, allowing you to fetch related data for each row in your result set.