MariaDB MINUS Operator

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

MINUS was supported since MariaDB 10.6.1 for compatibility with Oracle.

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

To use MINUS, please set to ORACLE mode first:

SET SESSION sql_mode='ORACLE';

MariaDB MINUS Syntax

Here is the syntax of the MariaDB MINUS operator:

rs1 MINUS rs2
rs1 MINUS ALL rs2

Parameters

rs1

Optional. A result set.

rs2

Optional. Another result set.

Both result sets must have the same columns.

Return value

rs1 MINUS rs2 returns the difference of two result sets. That is, MINUS return those rows that exist only in the first result set but not in the second result set.

MariaDB MINUS 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
MINUS
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
MINUS
SELECT * FROM test_number_1;

Output:

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

Note that MINUS returns distinct rows by default, if you want to get all the rows, please use MINUS ALL.

SELECT * FROM test_number_1
MINUS ALL
SELECT * FROM test_number_2;

Output:

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

Conclusion

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