In MariaDB, the MINUS
operator returns distinct rows from the left input query that aren’t output by the right input query.
The MINUS
operator was introduced in MariaDB 10.6.1 as a synonym for the EXCEPT
operator for the purposes of compatibility with Oracle. Therefore, we can use MINUS
and EXCEPT
interchangeably (in MariaDB 10.6.1 and later).
However, I’ve found that the MINUS
operator only works when my sql_mode = "oracle"
. Although this isn’t explicitly mentioned in MariaDB’s documentation, it is implied in the task for implementing the MINUS
operator in MariaDB.
Sample Data
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 MINUS
operator to return teachers that aren’t also students.
Set sql_mode
to Oracle
Before we start using the MINUS
operator, let’s set our sql_mode
to oracle
:
SET sql_mode = "oracle";
OK, now we can go ahead and use the MINUS
operator.
Example of MINUS
SELECT TeacherName FROM Teachers
MINUS
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
MINUS
SELECT TeacherName FROM Teachers;
Result:
+-------------+ | StudentName | +-------------+ | Faye | | Jet | | Spike | | Ein | +-------------+
It’s possible to get the same result without using the MINUS
(or 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 | +-------------+
Include Duplicates
By default, the MINUS
operator implicitly applies a DISTINCT
operation. In other words, it returns only distinct values by default. But we can specify MINUS ALL
to include duplicates in the result:
SELECT TeacherName FROM Teachers
MINUS 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
MINUS 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.
Not in Oracle Mode?
Here’s what happens when we attempt to use MINUS
when not in Oracle mode.
Let’s reset our sql_mode
to the default setting:
SET sql_mode = default;
Now let’s try using the MINUS
operator again:
SELECT TeacherName FROM Teachers
MINUS
SELECT StudentName FROM Students;
Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT StudentName FROM Students' at line 3