MySQL - EXISTS Operator

MySQL - The EXISTS Operator

The EXISTS operator is a boolean operator used in a WHERE clause to check for the presence of any rows in a subquery. It returns TRUE if the subquery returns one or more rows, and FALSE otherwise. A key performance benefit is that it stops processing the subquery as soon as the first matching row is found.

Syntax

SELECT ...
FROM table1
WHERE [NOT] EXISTS (SELECT 1 FROM table2 WHERE condition);

Note: It’s a common convention to use SELECT 1 or SELECT * in the subquery. The actual columns selected do not matter, as EXISTS only checks for the presence of rows, not their content.

Setup: Sample Tables

Let’s create two tables for our examples: customers and orders.

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

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

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

INSERT INTO orders VALUES
(101, 1, '2023-10-26'),
(102, 3, '2023-10-25'),
(103, 1, '2023-10-27');

Using EXISTS with SELECT

A common use case is to find rows in one table that have a corresponding match in another. Let’s find all customers who have placed at least one order.

Example

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

Output

+---------------+
| customer_name |
+---------------+
| Alice         |
| Charlie       |
+---------------+

Comparison with INNER JOIN

You could achieve the same result with an INNER JOIN. However, JOIN can produce duplicate rows if a customer has multiple orders, requiring a DISTINCT. EXISTS avoids this naturally.

-- Using INNER JOIN, needs DISTINCT to avoid duplicates
SELECT DISTINCT c.customer_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Using NOT EXISTS

The NOT EXISTS operator is perfect for finding rows in one table that do not have a match in another. Let’s find all customers who have never placed an order.

Example

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

Output

+---------------+
| customer_name |
+---------------+
| Bob           |
+---------------+

Comparison with LEFT JOIN ... IS NULL

This is a classic pattern that can also be solved with a LEFT JOIN. Both are valid, and modern MySQL optimizers are often smart enough to treat them similarly. However, NOT EXISTS can be more readable and explicit in its intent.

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Using EXISTS with UPDATE and DELETE

EXISTS is also very useful for conditional UPDATE or DELETE statements.

Example: DELETE

Let’s delete all customers who have placed an order (a contrived example for demonstration).

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

This would delete Alice and Charlie from the customers table, leaving only Bob.

Using EXISTS in a Client Program

When checking for the existence of a record from an application, using an EXISTS query is highly efficient.

Example (Node.js with mysql2)

const mysql = require('mysql2/promise');

async function checkCustomerExists(connection, customerId) {
  try {
    const sql = 'SELECT EXISTS(SELECT 1 FROM customers WHERE customer_id = ?) AS `exists`';
    const [rows] = await connection.execute(sql, [customerId]);

    // The result is an integer: 1 if exists, 0 if not.
    return rows[0].exists === 1;

  } catch (error) {
    console.error('Failed to check customer existence:', error);
    return false;
  }
}

async function main() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_password',
      database: 'testdb'
    });

    const customerIdToCheck = 2; // Bob
    const exists = await checkCustomerExists(connection, customerIdToCheck);
    console.log(`Does customer with ID ${customerIdToCheck} exist? ${exists}`); // true

    const nonExistentId = 99;
    const exists2 = await checkCustomerExists(connection, nonExistentId);
    console.log(`Does customer with ID ${nonExistentId} exist? ${exists2}`); // false

  } catch (err) {
    console.error('An error occurred:', err);
  } finally {
    if (connection) connection.end();
  }
}

main();