MySQL EXISTS

Summary: in this tutorial, you will learn how to use the MySQL EXISTS operator and when to use it to improve the performance of the queries.

Introduction to MySQL EXISTS operator

The EXISTS operator is a boolean operator that returns either true or false. The EXISTS operator is often used to test for the existence of rows returned by the subquery.

The following illustrates the basic syntax of the EXISTS operator:

SELECT 
    select_list
FROM
    a_table
WHERE
    [NOT] EXISTS(subquery);Code language: SQL (Structured Query Language) (sql)

If the subquery returns at least one row, the EXISTS operator returns true, otherwise, it returns false.

In addition, the EXISTS operator terminates further processing immediately once it finds a matching row, which can help improve the performance of the query.

The NOT operator negates the EXISTS operator. In other words, the NOT EXISTS returns true if the subquery returns no row, otherwise it returns false.

Note that you can use SELECT *, SELECT column, SELECT a_constant, or anything in the subquery. The results are the same because MySQL ignores the select list that appeared in the SELECT clause.

MySQL EXISTS operator examples

Let’s take some examples of using the EXISTS operator to understand how it works.

MySQL SELECT EXISTS examples

Consider the following customers and orders tables in the sample database.

The following statement uses the EXISTS operator to find the customer who has at least one order:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS(
	SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber 
		= customers.customernumber);Code language: SQL (Structured Query Language) (sql)

Try It Out

In this example, for each row in the customers table, the query checks the customerNumber in the orders table. If the customerNumber, which appears in the customers table, exists in the orders table, the subquery returns the first matching row.

As a result, the EXISTS operator returns true and stops examining the orders table. Otherwise, the subquery returns no row, and the EXISTS operator returns false.

The following example uses the NOT EXISTS operator to find customers who do not have any orders:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    NOT EXISTS( 
	SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber
	);Code language: SQL (Structured Query Language) (sql)

Try It Out

MySQL UPDATE EXISTS examples

Suppose that you have to update the phone extensions of the employees who work at the office in San Francisco.

The following statement finds employees who work at the office in San Franciso:

SELECT 
    employeenumber, 
    firstname, 
    lastname, 
    extension
FROM
    employees
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco' AND 
           offices.officeCode = employees.officeCode);Code language: SQL (Structured Query Language) (sql)

Try It Out

This example adds the number 1 to the phone extension of employees who work at the office in San Francisco:

UPDATE employees 
SET 
    extension = CONCAT(extension, '1')
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco'
                AND offices.officeCode = employees.officeCode);Code language: SQL (Structured Query Language) (sql)

How it works.

• First, the EXISTS operator in the WHERE clause gets only employees who work at the office in San Fransisco.
• Second, the CONCAT() function concatenate the phone extension with the number 1.

MySQL INSERT EXISTS example

Suppose that you want to archive customers who don’t have any sales orders in a separate table. To do this, you use these steps:

First, create a new table for archiving the customers by copying the structure from the customers table:

CREATE TABLE customers_archive 
LIKE customers;Code language: SQL (Structured Query Language) (sql)

Second, insert customers who do not have any sales orders into the customers_archive table using the following INSERT statement.

INSERT INTO customers_archive
SELECT * 
FROM customers
WHERE NOT EXISTS( 
   SELECT 1
   FROM
       orders
   WHERE
       orders.customernumber = customers.customernumber
);Code language: SQL (Structured Query Language) (sql)

Try It Out

Third, query data from the customers_archive table to verify the insert operation.

SELECT * FROM customers_archive;Code language: SQL (Structured Query Language) (sql)

Try It Out

MySQL DELETE EXISTS example

One final task in archiving the customer data is to delete the customers that exist in the customers_archive table from the customers table.

To do this, you use the EXISTS operator in WHERE clause of the DELETE statement as follows:

DELETE FROM customers
WHERE EXISTS( 
    SELECT 
        1
    FROM
        customers_archive a
    
    WHERE
        a.customernumber = customers.customerNumber);Code language: SQL (Structured Query Language) (sql)

MySQL EXISTS operator vs. IN operator

To find the customer who has placed at least one order, you can use the IN operator as shown in the following query:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    customerNumber IN (
        SELECT 
            customerNumber
        FROM
            orders);Code language: SQL (Structured Query Language) (sql)

Try It Out

Let’s compare the query that uses the IN operator with the one that uses the EXISTS operator by using the EXPLAIN statement.

EXPLAIN SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber);Code language: SQL (Structured Query Language) (sql)

Now, check the performance of the query that uses the IN operator.

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    customerNumber IN (SELECT 
            customerNumber
        FROM
            orders);Code language: SQL (Structured Query Language) (sql)

Try It Out

The query that uses the EXISTS operator is much faster than the one that uses the IN operator.

The reason is that the EXISTS operator works based on the “at least found” principle. The EXISTS stops scanning the table when a matching row is found.

On the other hand, when the IN operator is combined with a subquery, MySQL must process the subquery first and then use the result of the subquery to process the whole query.

The general rule of thumb is that if the subquery contains a large volume of data, the EXISTS operator provides better performance.

However, the query that uses the IN operator will perform faster if the result set returned from the subquery is very small.

For example, the following statement uses the IN operator to select all employees who work at the office in San Francisco.

SELECT 
    employeenumber, 
    firstname, 
    lastname
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            offices.city = 'San Francisco');Code language: SQL (Structured Query Language) (sql)

Try It Out

Let’s check the performance of the query.

It is a little bit faster than the query that uses the EXISTS operator that we mentioned in the first example. See the performance of the query that uses the EXIST operator below:

In this tutorial, you have learned how to use the MySQL EXISTS operator to test for the existence of rows returned by a subquery.