MySQL EXCEPT

Summary: in this tutorial, you will learn how to use the MySQL EXCEPT operator to find the set difference between two sets of data.

Note that the EXCEPT operator is supported in MySQL starting from version 8.0.31. If you use a lower version, you can emulate the EXCEPT (or MINUS) operator.

Introduction to the MySQL EXCEPT operator

The MySQL EXCEPT operator allows you to retrieve rows from one query that do not appear in another query.

Here’s the basic syntax of the MySQL EXCEPT operator:

query1
EXCEPT [ALL | DISTINCT]
query2;Code language: SQL (Structured Query Language) (sql)

In this syntax, the EXCEPT will compare the result of query1 with the result set of query2 and return the rows of the result set of query1 that do not appear in the result set of query2.

By default, the EXCEPT operator uses the DISTINCT option if you omit it. The EXCEPT DISTINCT removes duplicate rows in the result set.

If you want to retain the duplicate rows, you need to specify the ALL option explicitly.

To use the EXCEPT operator, the query1 and query2 need to follow these rules:

• The order and the number of columns in the select list of the queries must be the same.
• The data types of the corresponding columns must be compatible.

The EXCEPT operator returns a query set with column names derived from the column names of the first query (query1).

MySQL EXCEPT operator examples

Let’s take some examples of using the MySQL EXCEPT operator.

1) Simple MySQL EXCEPT operator example

First, create two tables t1 and t2:

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 (
    id INT PRIMARY KEY
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the t1 and t2 tables:

INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);Code language: SQL (Structured Query Language) (sql)

Third, use the EXCEPT operator to find rows that appear in the table t1 but do not appear in the table t2:

SELECT id FROM t1
EXCEPT
SELECT id FROM t2;Code language: SQL (Structured Query Language) (sql)

Output:

+----+
| id |
+----+
|  1 |
+----+Code language: plaintext (plaintext)

In this example, the first query returns a result set (1,2,3) and the second query produces a result set (2,3,4).

The EXCEPT operator returns the row 1 that appears in the first result set but does not appear in the second result set.

The following Venn diagram illustrates the EXCEPT operation:

2) Practical MySQL EXCEPT operator example

We’ll use the employees and customers tables from the sample database to illustrate the EXCEPT operator:

The following query uses the EXCEPT operator to find the first names that appear in the customers table but do not appear in the employees table:

SELECT firstName
FROM employees
EXCEPT
SELECT contactFirstName
FROM customers;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| firstName |
+-----------+
| Gerard    |
| Loui      |
| Larry     |
| Anthony   |
| Pamela    |
| Andy      |
| Martin    |
| Barry     |
| Yoshimi   |
| Tom       |
| Diane     |
| Mami      |
| Foon Yue  |
| George    |
+-----------+
14 rows in set (0.03 sec)Code language: plaintext (plaintext)

In this example, the result set uses the firstName column of the first query for its column.

3) Using the EXCEPT operator with the ORDER BY clause example

To sort the result set returned by the EXCEPT operator, you use the ORDER BY clause.

For example, the following query uses the EXCEPT operator to get the first names that appear in the employees table but do not appear in the customers table and sort the first names alphabetically:

SELECT firstName
FROM employees
EXCEPT
SELECT contactFirstName
FROM customers
ORDER BY firstName;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| firstName |
+-----------+
| Andy      |
| Anthony   |
| Barry     |
| Diane     |
| Foon Yue  |
| George    |
| Gerard    |
| Larry     |
| Loui      |
| Mami      |
| Martin    |
| Pamela    |
| Tom       |
| Yoshimi   |
+-----------+
14 rows in set (0.01 sec)Code language: plaintext (plaintext)

4) Using the EXCEPT operator with the ALL option

The following example uses the EXCEPT operator with the ALL option to retain duplicate first names in the result set:

SELECT firstName
FROM employees
EXCEPT ALL
SELECT contactFirstName
FROM customers
ORDER BY firstName;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+
| firstName |
+-----------+
| Andy      |
| Anthony   |
| Barry     |
| Diane     |
| Foon Yue  |
| George    |
| Gerard    |
| Gerard    |
| Larry     |
| Loui      |
| Mami      |
| Martin    |
| Pamela    |
| Tom       |
| Yoshimi   |
+-----------+
15 rows in set (0.00 sec)
Code language: plaintext (plaintext)

In the result set, the first name Gerard appears twice.

Summary

• Use the MySQL EXCEPT operator to retrieve rows from one result set that do not appear in another result set.
• EXCEPT DISTINCT removes duplicates while the EXCEPT ALL retains the duplicates.
• The EXCEPT operator uses the DISTINCT option by default.