MySQL EXISTS Operator

The EXISTS operator in MySQL checks whether a subquery returns any rows. If the subquery returns at least one row, EXISTS evaluates to TRUE and the outer query's row is included in the result. If the subquery returns no rows, EXISTS is FALSE and the row is excluded.

How EXISTS Works

EXISTS does not care about what the subquery selects — only whether it returns results or not. Because of this, it is common to write SELECT 1 or SELECT * inside an EXISTS subquery.

Syntax

SELECT column_name
FROM table_name
WHERE EXISTS (SELECT 1 FROM other_table WHERE condition);

Sample Tables

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

INSERT INTO customers VALUES (1, 'Anita Roy');
INSERT INTO customers VALUES (2, 'Suresh Mehta');
INSERT INTO customers VALUES (3, 'Priya Nair');
INSERT INTO customers VALUES (4, 'Vikram Rao');

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_item VARCHAR(100)
);

INSERT INTO orders VALUES (101, 1, 'Laptop');
INSERT INTO orders VALUES (102, 2, 'Phone');
INSERT INTO orders VALUES (103, 1, 'Tablet');

Customers 3 and 4 have no orders.

Example: Customers Who Have Placed at Least One Order

SELECT customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

Result:

customer_name
-------------
Anita Roy
Suresh Mehta

For each customer, the subquery checks if at least one order exists with the matching customer_id. Priya Nair and Vikram Rao are excluded because no such order is found.

NOT EXISTS

NOT EXISTS returns rows where the subquery finds no matching result.

SELECT customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

Result:

customer_name
-------------
Priya Nair
Vikram Rao

These are customers who have never placed an order.

EXISTS vs IN

EXISTS with UPDATE

UPDATE customers
SET customer_name = CONCAT(customer_name, ' (Active)')
WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id
);

This marks only customers who have placed an order as Active.

Key Points

• EXISTS returns TRUE if the subquery produces at least one row.
• It is typically used with correlated subqueries that reference the outer query.
• NOT EXISTS finds rows with no matching record in the subquery.
• EXISTS stops searching after the first matching row, making it efficient.
• EXISTS handles NULL safely, unlike NOT IN which fails when the list contains NULL.