The MySQL EXCEPT operator is a set operation that returns rows that are present in the first result set but not in the second result set. It is similar to the UNION operator, which returns rows that are present in any of the result sets, but the EXCEPT operator only returns rows that are unique to the first result set.
The EXCEPT operator is available in MySQL 8.0.31 and later. It is not available in earlier versions of MySQL.
Syntax
The syntax for the EXCEPT operator is as follows:
SELECT * FROM table1 EXCEPT SELECT * FROM table2;
This query will return all rows from table1 that are not present in table2.
Example
Consider the following two tables:
table1 +----+--------------+-------+ | id | name | email | +----+--------------+-------+ | 1 | John Doe | aaa | | 2 | Jane Doe | bbb | | 3 | Peter Jones | ccc | +----+--------------+-------+ table2 +----+--------------+-------+ | id | name | email | +----+--------------+-------+ | 2 | Jane Doe | bbb | | 4 | Mary Smith | ddd | +----+--------------+-------+
The following query will return the rows from table1 that are not present in table2:
SELECT * FROM table1 EXCEPT SELECT * FROM table2;
This query will return the following result set:
+----+--------------+-------+ | id | name | email | +----+--------------+-------+ | 1 | John Doe | aaa | | 3 | Peter Jones | ccc | +----+--------------+-------+
DISTINCT and ALL
The EXCEPT operator can also be used with the DISTINCT and ALL keywords. The DISTINCT keyword specifies that only distinct rows should be returned. The ALL keyword specifies that all rows should be returned, even if they are duplicates.
For example, the following query will return only distinct rows from table1 that are not present in table2:
SELECT DISTINCT * FROM table1 EXCEPT SELECT DISTINCT * FROM table2;
This query will return the following result set:
+----+--------------+-------+ | id | name | email | +----+--------------+-------+ | 1 | John Doe | aaa | | 3 | Peter Jones | ccc | +----+--------------+-------+