Lesson 20: EXISTS and Correlated Subqueries

EXISTS checks whether a subquery returns at least one row. Unlike IN, it stops as soon as the first matching row is found, making it efficient for large datasets and safe in situations where NULL values may be present.

EXISTS executes the subquery for each row of the outer query and includes the row if a result exists.

Common real-world scenarios for using EXISTS:

• Finding missing data: Customers with no orders, products with no reviews (NOT EXISTS)
• Conditional existence checks: VIP customers with no recent orders (EXISTS + NOT EXISTS)
• Data integrity: FK integrity validation, detecting orphan records (NOT EXISTS)
• Universal negation: "Products purchased by every customer" (double NOT EXISTS)

EXISTS vs. IN

Basic EXISTS

-- Customers who have placed at least one order
SELECT id, name, grade
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
)
ORDER BY name
LIMIT 8;

When the inner query references c.id from the outer query, it is called a Correlated Subquery. It executes once for each outer row to check whether a matching order exists.

NOT EXISTS — Finding Missing Data

NOT EXISTS is a safe alternative to NOT IN when the subquery column may contain NULL values.

-- Customers who have never placed an order (safer than NOT IN)
SELECT id, name, email, created_at
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
)
ORDER BY created_at DESC
LIMIT 10;

Result (example):

-- Products on someone's wishlist but never purchased
SELECT p.id, p.name, p.price
FROM products AS p
WHERE EXISTS (
    SELECT 1 FROM wishlists AS w WHERE w.product_id = p.id
)
AND NOT EXISTS (
    SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id
)
ORDER BY p.price DESC;

Result (example):

Correlated Subqueries for Conditional Logic

Using a correlated subquery in the SELECT clause, you can check "does a related record exist?" for each row.

-- Show whether each customer has orders, reviews, and complaints
SELECT
    c.id,
    c.name,
    c.grade,
    CASE WHEN EXISTS (SELECT 1 FROM orders     WHERE customer_id = c.id) THEN 'Yes' ELSE 'No' END AS has_orders,
    CASE WHEN EXISTS (SELECT 1 FROM reviews    WHERE customer_id = c.id) THEN 'Yes' ELSE 'No' END AS has_reviews,
    CASE WHEN EXISTS (SELECT 1 FROM complaints WHERE customer_id = c.id) THEN 'Yes' ELSE 'No' END AS has_complaints
FROM customers AS c
WHERE c.grade IN ('VIP', 'GOLD')
ORDER BY c.name
LIMIT 8;

Result (example):

Multi-Condition EXISTS

-- Customers who both placed orders and filed complaints in 2024
SELECT c.id, c.name, c.grade
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
      AND o.ordered_at LIKE '2024%'
)
AND EXISTS (
    SELECT 1
    FROM complaints AS comp
    WHERE comp.customer_id = c.id
)
ORDER BY c.name;

Using EXISTS in HAVING (with Aggregation)

-- Categories that have at least one product with 50 or more reviews
SELECT
    cat.name    AS category,
    COUNT(p.id) AS product_count
FROM categories AS cat
INNER JOIN products AS p ON p.category_id = cat.id
GROUP BY cat.id, cat.name
HAVING EXISTS (
    SELECT 1
    FROM products  AS p2
    INNER JOIN reviews AS r ON r.product_id = p2.id
    WHERE p2.category_id = cat.id
    GROUP BY p2.id
    HAVING COUNT(r.id) >= 50
)
ORDER BY category;

How EXISTS Executes

To understand why EXISTS is efficient, you need to know its internal behavior.

Key: Short-circuit Evaluation

• EXISTS: Stops as soon as the first matching row is found. Even if a customer has 100 orders, only 1 needs to be checked.
• IN: Collects the entire result of the subquery first, then compares. The difference is significant with large datasets.
• Why use SELECT 1: EXISTS only checks for the existence of rows, so there is no need to retrieve column values. SELECT * also works, but SELECT 1 makes the intent clearer.

The NULL Trap with NOT IN

If the subquery result of NOT IN contains even one NULL, the entire result becomes empty. This is the primary reason to prefer NOT EXISTS.

-- ❌ Dangerous: if product_id has NULL, result is 0 rows!
SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);
-- If even one row has NULL product_id, all comparisons become UNKNOWN → no results

-- ✅ Safe: NOT EXISTS is not affected by NULL
SELECT name FROM products AS p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items AS oi
    WHERE oi.product_id = p.id
);

Rule: Use NOT EXISTS by default instead of NOT IN. Especially when the subquery column may contain NULL, always use NOT EXISTS.

Anti-Join Pattern Comparison

"Finding rows that don't exist" can be implemented in three ways in SQL. Here is a comparison of the pros and cons.

-- Method 1: NOT EXISTS (recommended)
SELECT c.name FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

-- Method 2: LEFT JOIN + IS NULL (equivalent)
SELECT c.name FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Method 3: NOT IN (NULL risk)
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

All three queries return the same result, but NOT IN returns an empty result if customer_id contains NULL. In practice, use NOT EXISTS or LEFT JOIN + IS NULL.

Summary

Practice Problems

Problem 1

Use NOT EXISTS to implement an anti-join to find orders where shipping has been created but delivery is not yet completed (delivered_at IS NULL). Return order_number, ordered_at, status, carrier, shipped_at.

SELECT
    o.order_number,
    o.ordered_at,
    o.status,
    s.carrier,
    s.shipped_at
FROM orders AS o
INNER JOIN shipping AS s ON s.order_id = o.id
WHERE NOT EXISTS (
    SELECT 1
    FROM shipping AS s2
    WHERE s2.order_id = o.id
      AND s2.delivered_at IS NOT NULL
)
ORDER BY s.shipped_at DESC
LIMIT 20;

Problem 2

Use EXISTS with correlated subqueries to find products that have both a 5-star and a 1-star review. Return product_id, product_name, price.

SELECT
    p.id    AS product_id,
    p.name  AS product_name,
    p.price
FROM products AS p
WHERE EXISTS (
    SELECT 1 FROM reviews WHERE product_id = p.id AND rating = 5
)
AND EXISTS (
    SELECT 1 FROM reviews WHERE product_id = p.id AND rating = 1
)
ORDER BY p.name;

Problem 3

Use correlated subqueries to display the highest-value order information for each staff member. Return staff_name, department, max_order_amount, max_order_number. max_order_number is the order number matching that amount.

SELECT
    s.name AS staff_name,
    s.department,
    (SELECT MAX(o.total_amount) FROM orders AS o WHERE o.staff_id = s.id) AS max_order_amount,
    (SELECT o.order_number FROM orders AS o
     WHERE o.staff_id = s.id
     ORDER BY o.total_amount DESC
     LIMIT 1) AS max_order_number
FROM staff AS s
WHERE EXISTS (
    SELECT 1 FROM orders WHERE staff_id = s.id
)
ORDER BY max_order_amount DESC
LIMIT 15;

Problem 4

Use NOT EXISTS to find customers who have never written a review but have placed 5 or more orders. Return customer_id, name, grade, order_count.

SELECT
    c.id AS customer_id,
    c.name,
    c.grade,
    (SELECT COUNT(*) FROM orders WHERE customer_id = c.id
        AND status NOT IN ('cancelled', 'returned')) AS order_count
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM reviews WHERE customer_id = c.id
)
AND (
    SELECT COUNT(*) FROM orders WHERE customer_id = c.id
        AND status NOT IN ('cancelled', 'returned')
) >= 5
ORDER BY order_count DESC
LIMIT 20;

Problem 5

Use EXISTS to find customers who have paid at least once with every payment method (credit_card, bank_transfer, cash, etc.). Return customer_id, name. Hint: Compare the number of payment method types with the number of methods used by each customer.

SELECT c.id AS customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT DISTINCT p2.method
    FROM payments AS p2
    WHERE p2.status = 'completed'

    EXCEPT

    SELECT p.method
    FROM payments AS p
    INNER JOIN orders AS o ON p.order_id = o.id
    WHERE o.customer_id = c.id
      AND p.status = 'completed'
)
AND EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
)
ORDER BY c.name;

Problem 6

Combine EXISTS with aggregate conditions to find categories that have at least one product with an average review rating of 4.0 or higher. Return category_name, product_count.

SELECT
    cat.name AS category_name,
    COUNT(p.id) AS product_count
FROM categories AS cat
INNER JOIN products AS p ON p.category_id = cat.id
WHERE p.is_active = 1
GROUP BY cat.id, cat.name
HAVING EXISTS (
    SELECT 1
    FROM products AS p2
    INNER JOIN reviews AS r ON r.product_id = p2.id
    WHERE p2.category_id = cat.id
    GROUP BY p2.id
    HAVING AVG(r.rating) >= 4.0
)
ORDER BY category_name;

Problem 7

Find all wishlist products that the customer has not yet purchased. Return customer_name, product_name, created_at (wishlist registration date). Use NOT EXISTS with a correlated subquery that checks for matching customer_id and product_id in order_items and orders.

SELECT
    c.name  AS customer_name,
    p.name  AS product_name,
    w.created_at
FROM wishlists AS w
INNER JOIN customers AS c ON w.customer_id = c.id
INNER JOIN products  AS p ON w.product_id  = p.id
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    WHERE o.customer_id  = w.customer_id
      AND oi.product_id  = w.product_id
      AND o.status NOT IN ('cancelled', 'returned')
)
ORDER BY w.created_at DESC
LIMIT 20;

Problem 8

Use NOT EXISTS to find products that were purchased in common by every customer who ordered in 2024. In other words, products where not a single customer who ordered in 2024 failed to purchase them. Return product_id, product_name.

SELECT p.id AS product_id, p.name AS product_name
FROM products AS p
WHERE NOT EXISTS (
    SELECT c.id
    FROM customers AS c
    WHERE EXISTS (
        SELECT 1 FROM orders AS o
        WHERE o.customer_id = c.id
          AND o.ordered_at LIKE '2024%'
          AND o.status NOT IN ('cancelled', 'returned')
    )
    AND NOT EXISTS (
        SELECT 1
        FROM order_items AS oi
        INNER JOIN orders AS o ON oi.order_id = o.id
        WHERE o.customer_id = c.id
          AND oi.product_id = p.id
          AND o.ordered_at LIKE '2024%'
          AND o.status NOT IN ('cancelled', 'returned')
    )
)
ORDER BY p.name;

Problem 9

Find customers who have both filed complaints and have return history. Return customer_id, name, grade, complaint_count, return_count. Use EXISTS for filtering, and use subquery aggregation or JOINs for counting.

SELECT
    c.id    AS customer_id,
    c.name,
    c.grade,
    (SELECT COUNT(*) FROM complaints WHERE customer_id = c.id) AS complaint_count,
    (SELECT COUNT(*) FROM orders AS o
                    INNER JOIN returns AS r ON r.order_id = o.id
                    WHERE o.customer_id = c.id)               AS return_count
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM complaints WHERE customer_id = c.id
)
AND EXISTS (
    SELECT 1
    FROM orders AS o
    INNER JOIN returns AS r ON r.order_id = o.id
    WHERE o.customer_id = c.id
)
ORDER BY complaint_count DESC;

Problem 10

Use EXISTS to find customers who have ordered products from at least 3 different categories. Return customer_id, name, category_count, sorted by category_count descending, limited to 10 rows.

SELECT
    c.id AS customer_id,
    c.name,
    (
        SELECT COUNT(DISTINCT p.category_id)
        FROM order_items AS oi
        INNER JOIN orders AS o ON oi.order_id = o.id
        INNER JOIN products AS p ON oi.product_id = p.id
        WHERE o.customer_id = c.id
    ) AS category_count
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM order_items AS oi
    INNER JOIN orders AS o ON oi.order_id = o.id
    INNER JOIN products AS p ON oi.product_id = p.id
    WHERE o.customer_id = c.id
    GROUP BY o.customer_id
    HAVING COUNT(DISTINCT p.category_id) >= 3
)
ORDER BY category_count DESC
LIMIT 10;

Scoring Guide

Problem Areas:

Next: Lesson 21: SELF JOIN and CROSS JOIN