Subquery using Exists

Editorial

Introduction

The EXISTS operator is a powerful tool for checking whether a subquery returns any rows. It's commonly used with correlated subqueries to find records that have (or don't have) related records in another table.

In this tutorial, you will learn:

How EXISTS and NOT EXISTS work
When to use EXISTS vs. IN vs. JOIN
Performance advantages of EXISTS
Common patterns and use cases

Understanding EXISTS

What EXISTS Does

EXISTS returns TRUE if the subquery returns at least one row, FALSE otherwise.

SELECT * FROM table_a a
WHERE EXISTS (
    SELECT 1 FROM table_b b
    WHERE b.a_id = a.id
);

How It Works

Generated visual representation from text diagram for Understanding EXISTS.

SELECT 1 Convention

Because EXISTS only checks for row existence, the SELECT clause doesn't matter:

-- All of these are equivalent:
WHERE EXISTS (SELECT 1 FROM ...)
WHERE EXISTS (SELECT * FROM ...)
WHERE EXISTS (SELECT 'hello' FROM ...)
WHERE EXISTS (SELECT column_name FROM ...)

SELECT 1 is a convention that signals "we only care about existence."

Visualization

Diagram showing EXISTS checking if a subquery returns any rows: TRUE if at least one row exists, FALSE if empty

EXISTS Operator -- Checking for Row Existence

EXISTS Syntax

Basic Syntax

SELECT columns
FROM table_a a
WHERE EXISTS (
    SELECT 1
    FROM table_b b
    WHERE b.foreign_key = a.primary_key  -- Correlation
    [AND other_conditions]
);

NOT EXISTS Syntax

SELECT columns
FROM table_a a
WHERE NOT EXISTS (
    SELECT 1
    FROM table_b b
    WHERE b.foreign_key = a.primary_key
);

NOT EXISTS returns TRUE when the subquery returns no rows.

Use Case 1: Finding Matching Records

Find Orders That Have Items

SELECT o.order_id, o.order_status
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1
    FROM olist_order_items_dataset oi
    WHERE oi.order_id = o.order_id
)
LIMIT 10;

Find Orders That Have Payments

SELECT o.order_id, o.order_status
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1
    FROM olist_order_payments_dataset p
    WHERE p.order_id = o.order_id
)
LIMIT 10;

Multiple EXISTS Conditions

-- Orders that have BOTH items AND payments
SELECT o.order_id
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1 FROM olist_order_items_dataset oi
    WHERE oi.order_id = o.order_id
)
AND EXISTS (
    SELECT 1 FROM olist_order_payments_dataset p
    WHERE p.order_id = o.order_id
)
LIMIT 10;

Use Case 2: Finding Missing Records (NOT EXISTS)

Find Orders Without Reviews

SELECT o.order_id, o.order_status, o.order_delivered_customer_date
FROM olist_orders_dataset o
WHERE NOT EXISTS (
    SELECT 1
    FROM olist_order_reviews_dataset r
    WHERE r.order_id = o.order_id
)
AND o.order_status = 'delivered'
LIMIT 10;

Find Sellers Without Sales

SELECT s.seller_id, s.seller_city, s.seller_state
FROM olist_sellers_dataset s
WHERE NOT EXISTS (
    SELECT 1
    FROM olist_order_items_dataset oi
    WHERE oi.seller_id = s.seller_id
)
LIMIT 10;

Find Products Never Ordered

SELECT p.product_id, p.product_category_name
FROM olist_products_dataset p
WHERE NOT EXISTS (
    SELECT 1
    FROM olist_order_items_dataset oi
    WHERE oi.product_id = p.product_id
)
LIMIT 10;

EXISTS vs. IN

Equivalence

For simple cases, EXISTS and IN can achieve the same result:

-- Using IN
SELECT * FROM olist_orders_dataset
WHERE order_id IN (
    SELECT order_id FROM olist_order_payments_dataset
);

-- Using EXISTS (equivalent)
SELECT * FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1 FROM olist_order_payments_dataset p
    WHERE p.order_id = o.order_id
);

Key Differences

Aspect	IN	EXISTS
NULL Handling	NULL in list causes issues with NOT IN	NULL-safe
Performance	May load entire subquery result	Stops at first match
Readability	Simpler for value lists	Clearer for existence checks
Correlation	Typically non-correlated	Typically correlated

NULL Safety: Critical Difference!

-- ❌ NOT IN fails with NULLs!
SELECT * FROM orders
WHERE customer_id NOT IN (
    SELECT customer_id FROM blacklist  -- If ANY NULL exists, returns no rows!
);

-- ✅ NOT EXISTS is NULL-safe
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b
    WHERE b.customer_id = o.customer_id
);

EXISTS vs. JOIN

When Results Are Same

-- Using EXISTS
SELECT o.order_id
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1 FROM olist_order_items_dataset oi
    WHERE oi.order_id = o.order_id
);

-- Using JOIN (same result for one-to-one)
SELECT DISTINCT o.order_id
FROM olist_orders_dataset o
JOIN olist_order_items_dataset oi ON o.order_id = oi.order_id;

When Results Differ

JOIN can create duplicates if there's a one-to-many relationship:

-- EXISTS: Returns each order ONCE
SELECT COUNT(*)
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1 FROM olist_order_items_dataset oi
    WHERE oi.order_id = o.order_id
);
-- Result: ~99,441 rows (unique orders)

-- JOIN without DISTINCT: Returns duplicates
SELECT COUNT(*)
FROM olist_orders_dataset o
JOIN olist_order_items_dataset oi ON o.order_id = oi.order_id;
-- Result: ~112,650 rows (includes duplicates for multi-item orders)

Which to Choose

Need	Use
Only check existence, no duplicates	EXISTS
Need columns from related table	JOIN
Anti-join (find missing)	NOT EXISTS or LEFT JOIN + IS NULL

Performance Advantage

Short-Circuit Evaluation

EXISTS stops searching as soon as it finds ONE matching row:

Generated visual representation from text diagram for Performance Advantage.

When EXISTS is Faster

Checking existence in large tables
When matching rows are common (found quickly)
With proper indexes on join columns

Conditional EXISTS Patterns

Filtering Within EXISTS

-- Orders with HIGH-VALUE items (not just any items)
SELECT o.order_id, o.order_status
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1
    FROM olist_order_items_dataset oi
    WHERE oi.order_id = o.order_id
    AND oi.price > 500              -- Additional filter
)
LIMIT 10;

Multiple Conditions in EXISTS

-- Orders with items from specific sellers in specific state
SELECT o.order_id
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1
    FROM olist_order_items_dataset oi
    JOIN olist_sellers_dataset s ON oi.seller_id = s.seller_id
    WHERE oi.order_id = o.order_id
    AND s.seller_state = 'SP'
)
LIMIT 10;

Combining EXISTS and NOT EXISTS

-- Orders that have items but NO reviews
SELECT o.order_id, o.order_status
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1 FROM olist_order_items_dataset oi
    WHERE oi.order_id = o.order_id
)
AND NOT EXISTS (
    SELECT 1 FROM olist_order_reviews_dataset r
    WHERE r.order_id = o.order_id
)
LIMIT 10;

Practice

Let's practice using EXISTS and NOT EXISTS.

Exercise 1: Find Orders with Payments

Find orders that have associated payment records:

-- Find orders that have payment records

SELECT 
    o.order_id,
    o.order_status,
    o.order_purchase_timestamp
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1
    FROM olist_order_payments_dataset p
    WHERE p.order_id = o.order_id
)
ORDER BY o.order_purchase_timestamp DESC
LIMIT 10;

Exercise 2: Find Customers Without Orders

Find customers who have never placed an order (if any):

-- Find customers without any orders

SELECT 
    c.customer_id,
    c.customer_city,
    c.customer_state
FROM olist_customers_dataset c
WHERE NOT EXISTS (
    SELECT 1
    FROM olist_orders_dataset o
    WHERE o.customer_id = c.customer_id
)
LIMIT 10;

-- Note: In this dataset, all customers have orders
-- because customers are created when they place orders

Exercise 3: Orders with Multiple Item Types

Find orders that have more than one distinct product:

-- Find orders with multiple distinct products

SELECT o.order_id, o.order_status
FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1
    FROM olist_order_items_dataset oi1
    JOIN olist_order_items_dataset oi2 
        ON oi1.order_id = oi2.order_id 
        AND oi1.product_id < oi2.product_id
    WHERE oi1.order_id = o.order_id
)
LIMIT 10;

Exercise 4: Products with Reviews

Find products that have been reviewed (through orders):

-- Find products that have at least one review

SELECT DISTINCT
    p.product_id,
    p.product_category_name
FROM olist_products_dataset p
WHERE EXISTS (
    SELECT 1
    FROM olist_order_items_dataset oi
    JOIN olist_order_reviews_dataset r ON oi.order_id = r.order_id
    WHERE oi.product_id = p.product_id
)
LIMIT 10;

Common Mistakes

1. Forgetting Correlation

-- ❌ Non-correlated: Always returns TRUE if any items exist
SELECT * FROM olist_orders_dataset
WHERE EXISTS (
    SELECT 1 FROM olist_order_items_dataset
    -- Missing: WHERE order_id = o.order_id
);

-- ✅ Correlated: Checks for THIS order's items
SELECT * FROM olist_orders_dataset o
WHERE EXISTS (
    SELECT 1 FROM olist_order_items_dataset oi
    WHERE oi.order_id = o.order_id
);

2. Using NOT IN Instead of NOT EXISTS with NULLs

-- ❌ Dangerous: If subquery returns any NULL, NO rows returned
WHERE customer_id NOT IN (SELECT customer_id FROM table_with_nulls)

-- ✅ Safe: NOT EXISTS handles NULLs correctly
WHERE NOT EXISTS (SELECT 1 FROM table WHERE customer_id = outer.customer_id)

3. Unnecessary Complexity

-- ❌ Overcomplicated
WHERE (SELECT COUNT(*) FROM items WHERE order_id = o.order_id) > 0

-- ✅ Simple and efficient
WHERE EXISTS (SELECT 1 FROM items WHERE order_id = o.order_id)

Summary

Key Takeaways

EXISTS checks for row existence, not values
- Returns TRUE if subquery returns ≥1 row
- Returns FALSE if subquery returns 0 rows
NOT EXISTS finds missing relationships
- Perfect for anti-joins
- NULL-safe (unlike NOT IN)
Performance advantage:
- Short-circuit evaluation (stops at first match)
- Can be faster than IN for large datasets
Use SELECT 1 as a convention (content doesn't matter)
Comparison:
- EXISTS vs IN: EXISTS is NULL-safe, often faster
- EXISTS vs JOIN: EXISTS avoids duplicates

Quick Reference

-- Find matching records
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM items i WHERE i.order_id = o.order_id
);

-- Find missing records
SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.order_id = o.order_id
);

-- With additional conditions
WHERE EXISTS (
    SELECT 1 FROM items i 
    WHERE i.order_id = o.order_id AND i.price > 100
);

What's Next?

Now let's wrap up with a comprehensive Subquery Summary that ties all these concepts together.