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