MySQL EXISTS?

Summary: in this tutorial, you will learn how to use the MySQL EXISTS operator and when to use it to increase 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 the in a subquery to test for an “exist” condition.

The following illustrates the common usage of the EXISTS operator.

1 2 3 4 5 6

SELECT     select_list FROM     a_table WHERE     [NOT] EXISTS(subquery);

If the subquery returns any 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. Because of this characteristic, you can use the EXISTS operator to improve the performance of the query in some cases.

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.

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 appears in the SELECT clause.

MySQL EXISTS examples

MySQL SELECT EXISTS example

Let’s take a look at the customers and orders tables in the sample database.

Suppose you want to find the customer who has placed at least one sales order, you use the EXISTSoperator as follows:

1 2 3 4 5 6 7 8 9 10 11

SELECT     customerNumber, customerName FROM     customers WHERE     EXISTS( SELECT             1         FROM             orders         WHERE             orders.customernumber = customers.customernumber);

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 the result, the EXISTS operator returns true and stops scanning the orders table. Otherwise, the subquery returns no row and the EXISTS operator returns false.

To get the customer who has not placed any sales orders, you use the NOT EXISTS operator as the following statement:

1 2 3 4 5 6 7 8 9 10 11

SELECT     customerNumber, customerName FROM     customers WHERE     NOT EXISTS( SELECT             1         FROM             orders         WHERE             orders.customernumber = customers.customernumber);

MySQL UPDATE EXISTS example

Assume that you have to update the phone’s extensions of the employees who work at the San Francisco office.

To find employees who work at the San Franciso office, you use the EXISTS operator as the following UPDATE statement:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     employeenumber, firstname, lastname, extension FROM     employees WHERE     EXISTS( SELECT             1         FROM             offices         WHERE             city = 'San Francisco'                 AND offices.officeCode = employees.officeCode);

Suppose you want to add the number 5 at every phone’s extension of the employees who work at the San Francisco office, you can use the EXISTS operator in WHERE clause of the UPDATE statement as follows:

1 2 3 4 5 6 7 8 9 10 11

UPDATE employees SET     extension = CONCAT(extension, '1') WHERE     EXISTS( SELECT             1         FROM             offices         WHERE             city = 'San Francisco'                 AND offices.officeCode = employees.officeCode);

MySQL INSERT EXISTS example

Suppose you want to archive the customers who have not placed any sales order in a separate table. To achieve this, you follow the steps below.

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

1	CREATE TABLE customers_archive LIKE customers;

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

1 2 3 4 5 6 7 8

INSERT INTO customers_archive SELECT * FROM customers WHERE NOT EXISTS( SELECT             1         FROM             orders         WHERE             orders.customernumber = customers.customernumber);

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

1 2 3 4

SELECT     * FROM     customers_archive;

MySQL DELETE EXISTS example

One final task in archiving the customers 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:

1 2 3 4 5 6 7 8 9

DELETE FROM customers WHERE     EXISTS( SELECT         1     FROM         customers_archive a          WHERE         a.customernumber = customers.customerNumber);

MySQL EXISTS vs. IN

To find the customer who has placed at least one sales order, you can use the IN operator as follows:

1 2 3 4 5 6 7 8 9

SELECT     customerNumber, customerName FROM     customers WHERE     customerNumber IN (SELECT             customerNumber         FROM             orders);

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

1 2 3 4 5 6 7 8 9 10 11

EXPLAIN SELECT     customerNumber, customerName FROM     customers WHERE     EXISTS( SELECT             1         FROM             orders         WHERE             orders.customernumber = customers.customernumber);

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

1 2 3 4 5 6 7 8 9

SELECT     customerNumber, customerName FROM     customers WHERE     customerNumber IN (SELECT             customerNumber         FROM             orders);

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. It returns true and stops scanning table once at least one matching row found.

On the other hands, when the IN operator is combined with a subquery, MySQL must process the subquery first and then uses 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 a 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 selects all employees who work at the office in San Francisco.

1 2 3 4 5 6 7 8 9 10 11

SELECT     employeenumber, firstname, lastname FROM     employees WHERE     officeCode IN (SELECT             officeCode         FROM             offices         WHERE             offices.city = 'San Francisco');

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, we have discussed the MySQL EXISTS operator and introduced you to some guidelines for using the EXISTS operator to improve the query’s performance.