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.