MariaDB EXCEPT Operator

In MariaDB, EXCEPT is a built-in set operator that returns the difference of two result sets.

MariaDB supported EXCEPT since MariaDB 10.4.0.

In MariaDB 10.6.1 and later, you can use MINUS instead of EXCEPT.

MariaDB EXCEPT Syntax

Here is the syntax of the MariaDB EXCEPT operator:

rs1 EXCEPT rs2
rs1 EXCEPT ALL rs2

Parameters

rs1

Optional. A result set.

rs2

Optional. Another result set.

Both result sets must have the same columns.

Return value

rs1 EXCEPT rs2 returns the difference of rs1 and rs2. That is, EXCEPT returns those rows that only exist in the first result set but not in the second result set.

MariaDB EXCEPT Examples

First, let’s create two tables test_number_1 and test_number_2 to demonstrate the example:

DROP TABLE IF EXISTS test_number_1;
CREATE TABLE test_number_1 (
    num INT NOT NULL
);

DROP TABLE IF EXISTS test_number_2;
CREATE TABLE test_number_2 (
    num INT NOT NULL
);

Then, let’s insert a few rows:

INSERT INTO test_number_1
VALUES (0), (1), (1), (2), (3), (4), (4);
INSERT INTO test_number_2
VALUES (3), (4), (4), (5), (6), (7), (8);

Then, let’s look at the rows in the test_number_1 table:

SELECT * FROM test_number_1;

Output:

+-----+
| num |
+-----+
|   0 |
|   1 |
|   1 |
|   2 |
|   3 |
|   4 |
|   4 |
+-----+

Let’s look at the rows in the test_number_2 table:

SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   3 |
|   4 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
+-----+

If you want to get numbers that only exist in the test_number_1 table, use the following statement:

SELECT * FROM test_number_1
EXCEPT
SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   0 |
|   1 |
|   2 |
+-----+

If you want to get numbers that only exist in the test_number_2 table, use the following statement:

SELECT * FROM test_number_2
EXCEPT
SELECT * FROM test_number_1;

Output:

+-----+
| num |
+-----+
|   5 |
|   6 |
|   7 |
|   8 |
+-----+

Note that EXCEPT returns the distinct rows default, if you want to get all the rows, please use EXCEPT ALL, which is supported in MariaDB 10.5.0.

SELECT * FROM test_number_1
EXCEPT ALL
SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   0 |
|   1 |
|   2 |
|   1 |
+-----+

Conclusion

In MariaDB, EXCEPT is a built-in set operator that returns the difference of two result sets.