How to Use the EXISTS and NOT EXISTS Operator in MySQL

08/04/2021

Contents

In this article, you will learn how to use the EXISTS and NOT EXISTS operator in MySQL.

Using the EXISTS and NOT EXISTS operator in MySQL

The EXISTS and NOT EXISTS operators are used in MySQL to check whether a subquery returns any rows.

Syntax

The basic syntax of the EXISTS operator in MySQL is as follows:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

In this syntax, the subquery is enclosed in parentheses after the EXISTS keyword. The subquery should return at least one row for the EXISTS operator to return true. The NOT EXISTS operator is used in a similar way, but it returns true if the subquery does not return any rows.

Examples

Suppose we have two tables called “employees” and “departments”, with columns “id”, “name”, and “department_id”. We want to select all departments that have at least one employee.

SELECT name
FROM departments
WHERE EXISTS (SELECT * FROM employees WHERE department_id = departments.id);

In this example, the subquery checks whether there are any employees in the “employees” table with the same “department_id” as the current department in the outer query. If the subquery returns at least one row, the EXISTS operator returns true, and the department name is included in the result set.

And suppose we have a table called “orders” with columns “id”, “customer_id”, and “order_date”. We want to select all customers who have not placed an order in the last 30 days.

SELECT name
FROM customers
WHERE NOT EXISTS (SELECT * FROM orders WHERE customer_id = customers.id AND order_date > NOW() - INTERVAL 30 DAY);

In this example, the subquery checks whether there are any orders in the “orders” table with the same “customer_id” as the current customer in the outer query, and an “order_date” within the last 30 days. If the subquery does not return any rows, the NOT EXISTS operator returns true, and the customer name is included in the result set.

EXISTS vs. JOIN

The EXISTS operator can also be used as an alternative to a JOIN clause in some cases. Here is an example:

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

This query returns the same result as the following query using a JOIN clause:

SELECT DISTINCT customers.name
FROM customers
JOIN orders ON customers.id = orders.customer_id;

In some cases, using the EXISTS operator may be more efficient than using a JOIN clause, especially when the subquery returns a large number of rows.