In Oracle Database, the MINUS operator is used to return only unique rows returned by the first query but not by the second.
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 MINUS operator to return teachers that aren’t also students:
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.
We can get different results, depending on which table is on the left and which is on the right. Here’s an example that puts the Students table on the left and Teachers on the right:
SELECT StudentName FROM Students
MINUS
SELECT TeacherName FROM Teachers;Result:
| STUDENTNAME |
|---|
| Ein |
| Faye |
| Jet |
| Spike |
This time we get students that aren’t also teachers.
The MINUS operator returns distinct rows only. So in our example, only one row is returned for Cathy, even though there are two teachers with that name.
An Alternative
It’s possible to get the same result without using the MINUS 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 |
MINUS Equivalents in Other RDBMSs
Oracle’s MINUS operator is similar to the EXCEPT operator that many other RDBMSs use. MariaDB has an EXCEPT operator, but it has also introduced a MINUS operator as a synonym that can be used when in Oracle mode.