MySQL EXISTS

Testing for the existence of rows in subqueries

🔍 What is EXISTS?

The EXISTS operator tests whether a subquery returns any rows. It returns TRUE if the subquery contains rows, and FALSE if empty, making it perfect for checking related data existence. 


-- Check if customers have orders
SELECT customer_name 
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id
);

Output:

customer_name
John Smith
Sarah Johnson

Key EXISTS Concepts

✅

Basic EXISTS

Check if related records exist 

SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.product_id = p.id
);
❌

NOT EXISTS

Find records without matches 

SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);
🔗

Correlated Subquery

Subquery references outer query 

SELECT name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.manager_id = e.id
);
⚡

Performance

Stops at first match found 

-- Efficient: stops when found
WHERE EXISTS (
    SELECT 1 FROM table
    WHERE condition
);

🔹 Basic EXISTS Example

The EXISTS operator is commonly used to find records that have related data in another table. It returns TRUE when the subquery finds at least one matching row, making it efficient for existence checks. 

-- Find customers who have placed orders
SELECT 
    customer_id,
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Output:

customer_id customer_name email
1 John Smith [email protected]

🔹 NOT EXISTS Example

Use NOT EXISTS to find records that don't have related data. This is perfect for finding customers without orders, products never sold, or employees without assignments in your database. 

-- Find customers who have never placed an order
SELECT 
    customer_id,
    customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

Output:

customer_id customer_name
5 Mike Wilson

🔹 EXISTS with Multiple Conditions

You can add multiple conditions inside the EXISTS subquery to make your searches more specific. This allows you to check for existence based on complex criteria like date ranges, status values, or price thresholds. 

-- Find products with recent orders (last 30 days)
SELECT 
    product_id,
    product_name,
    price
FROM products p
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.product_id = p.product_id
    AND o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
);

Output:

product_id product_name price
101 Laptop 999.99

🔹 EXISTS vs IN Comparison

While both EXISTS and IN can check for matching values, EXISTS is often faster for large datasets because it stops searching once it finds the first match, whereas IN must retrieve all matching values. 

-- Using EXISTS (recommended for large tables)
SELECT customer_name 
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

-- Using IN (works but slower for large datasets)
SELECT customer_name 
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);

🧠 Test Your Knowledge

What does EXISTS return when the subquery finds matching rows?