MySQL EXISTS Operator

MySQL EXISTS Operator: Testing Subquery Matches

The MySQL EXISTS operator tests whether a correlated subquery returns at least one row. As soon as the subquery finds a single matching row, MySQL stops scanning and returns true (1), making EXISTS fast for existence checks.

Basic Syntax

Using SELECT 1 inside EXISTS (...) is a common convention because MySQL ignores the selected column list and checks only whether a row exists:

SELECT columns
FROM parent_table AS p
WHERE EXISTS (
    SELECT 1
    FROM child_table AS c
    WHERE c.parent_id = p.id
);

Practical SQL Examples

The first query finds customers in demo_customers who placed at least one paid order; the second uses NOT EXISTS to find products in demo_products that have never been ordered:

-- Customers with at least one paid order
SELECT c.id, c.first_name, c.email
FROM demo_customers AS c
WHERE EXISTS (
    SELECT 1
    FROM demo_orders AS o
    WHERE o.customer_id = c.id
      AND o.status = 'paid'
);

-- Products with zero recorded orders
SELECT p.id, p.product_name, p.price
FROM demo_products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM demo_orders AS o
    WHERE o.product_id = p.id
);

Common Mistakes and Best Practices

• Prefer NOT EXISTS Over NOT IN for Subqueries: If a NOT IN (subquery) result contains a single NULL, the entire query returns zero rows. NOT EXISTS checks row existence directly and never breaks on NULL values in unrelated columns.
• Avoid Duplicate Parent Rows: Unlike INNER JOIN, EXISTS never multiplies parent rows when a customer has ten orders—it simply returns each matching customer once without needing DISTINCT.

Frequently Asked Questions

Does SELECT * inside EXISTS (SELECT * ...) hurt performance?

No, the MySQL optimizer ignores the SELECT list inside EXISTS and only evaluates the FROM and WHERE predicates.

Is EXISTS always faster than IN?

MySQL 8 optimizes both as semi-joins when uncorrelated, though EXISTS handles multi-column correlations and NOT EXISTS NULL safety much better.

Can I use EXISTS in UPDATE or DELETE statements?

Yes, DELETE FROM demo_customers c WHERE NOT EXISTS (SELECT 1 FROM demo_orders o WHERE o.customer_id = c.id); safely removes inactive leads.