In MariaDB, the EXCEPT operator returns rows from the left input query that aren’t output by the right input query.

Another way of putting it is that it returns all rows from the left SELECT result set except rows that are in right SELECT result set.

Syntax

The official syntax goes like this:

SELECT ...
(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT]) SELECT ...
[(INTERSECT [ALL | DISTINCT] | EXCEPT [ALL | DISTINCT] | UNION [ALL | DISTINCT]) SELECT ...]
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

The above also includes the INTERSECT and UNION operators in the syntax, as the same syntax applies to those operators.

From MariaDB 10.4.0, parentheses can be used to specify precedence.

Example

Suppose we have the following tables:

SELECT * FROM Teachers;
SELECT * FROM Students;

Result:

+-----------+-------------+
| TeacherId | TeacherName |
+-----------+-------------+
|         1 | Warren      |
|         2 | Ben         |
|         3 | Cathy       |
|         4 | Cathy       |
|         5 | Bill        |
|         6 | Bill        |
+-----------+-------------+

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
|         1 | Faye        |
|         2 | Jet         |
|         3 | Spike       |
|         4 | Ein         |
|         5 | Warren      |
|         6 | Bill        |
+-----------+-------------+

We can use the EXCEPT operator to return teachers that aren’t also students:

SELECT TeacherName FROM Teachers
EXCEPT
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Ben         |
| Cathy       |
+-------------+

So we only get values that appear in the Teachers table that don’t also appear in the Students table.

By default, it returns distinct rows, so only one row is returned for Cathy, even though there are two teachers with that name. We can change this behaviour – more on this later.

We can also switch it around and put the Students table on the left and Teachers on the right.

SELECT StudentName FROM Students
EXCEPT
SELECT TeacherName FROM Teachers;

Result:

+-------------+
| StudentName |
+-------------+
| Faye        |
| Jet         |
| Spike       |
| Ein         |
+-------------+

It’s possible to get the same result without using the EXCEPT operator. For example, we could rewrite our first example to this:

SELECT 
    DISTINCT TeacherName
FROM Teachers t 
WHERE NOT EXISTS (SELECT StudentName FROM Students s
WHERE t.TeacherName = s.StudentName);

Result:

+-------------+
| TeacherName |
+-------------+
| Ben         |
| Cathy       |
+-------------+

Mind you, the EXCEPT operator helps to simplify the code quite significantly.

Include Duplicates

By default, the EXCEPT operator implicitly applies a DISTINCT operation. In other words, it returns only distinct values by default.

Prior to MariaDB 10.5.0, the implicit DISTINCT was our only option – we were unable to specify ALL. However, MariaDB 10.5.0 introduced the EXCEPT ALL and EXCEPT DISTINCT syntax.

This means that we can now do queries like this:

SELECT TeacherName FROM Teachers
EXCEPT ALL
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Cathy       |
| Ben         |
| Cathy       |
| Bill        |
+-------------+

This time we got four rows, instead of the two that we got in our first example.

We can see that both Cathys were returned instead of just one like in our first example.

As for Bill? There are two Bills in the Teachers table, but only one is returned here. That’s probably because there’s one Bill in the Students table, which would exclude one of the Bills from our results.

And here’s an example that explicitly uses the DISTINCT operator:

SELECT TeacherName FROM Teachers
EXCEPT DISTINCT
SELECT StudentName FROM Students;

Result:

+-------------+
| TeacherName |
+-------------+
| Ben         |
| Cathy       |
+-------------+

As expected, we get the same result that we would get if we were to remove the DISTINCT operator.

In MariaDB 10.6.1, MINUS was introduced as a synonym for EXCEPT. Therefore, we can use MINUS instead of EXCEPT in MariaDB 10.6.1 and later.