Subqueries with EXISTS in MySQL

Introduction

Use EXISTS to check if some related data exists without returning the data itself. It helps answer yes/no questions about data presence.

You want to find customers who have placed at least one order.

You want to check if a product has any reviews before showing it.

You want to list employees who manage at least one team member.

You want to filter records only if related records exist in another table.

Syntax

MySQL

SELECT column_list
FROM table_name
WHERE EXISTS (
    SELECT 1
    FROM related_table
    WHERE condition
);

EXISTS returns TRUE if the subquery finds any rows, otherwise FALSE.

The subquery inside EXISTS usually uses a condition to link to the outer query.

Examples

Finds customers who have at least one order.

MySQL

SELECT name
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.id
);

Lists products that have reviews.

MySQL

SELECT product_name
FROM products
WHERE EXISTS (
    SELECT 1
    FROM reviews
    WHERE reviews.product_id = products.id
);

Finds employees who manage others.

MySQL

SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE manager_id = e.id
);

Sample Program

This query lists customers who have placed at least one order. Alice has 2 orders, Carol has 1, Bob has none.

MySQL

CREATE TABLE customers (id INT, name VARCHAR(50));
CREATE TABLE orders (id INT, customer_id INT);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 3);

SELECT name
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.id
);

Important Notes

EXISTS stops checking as soon as it finds one matching row, so it is efficient for existence checks.

The subquery inside EXISTS does not return data to the outer query, only TRUE or FALSE.

You can use NOT EXISTS to find rows where no related data exists.

Summary

EXISTS checks if related rows exist and returns TRUE or FALSE.

Use EXISTS in WHERE to filter rows based on related data presence.

It is efficient because it stops searching after finding the first match.