The MINUS operator is used with two SELECT statements to return only those rows from the first query that are not present in the second. It filters out all common rows and keeps only the unique rows from the first result set.

Pictorial Representation :

As you can see is in the above diagram, the MINUS operator will return only those rows which are present in the result set from Table1 and not present in the result set of Table2.

Syntax:

SELECT column1, column2, ..., columnN
FROM table_name1
[WHERE condition]
MINUS
SELECT column1, column2, ..., columnN
FROM table_name2
[WHERE condition];

In the above syntax

First SELECT: Retrieves specified columns (column1, column2, ..., columnN) from table_name1, optionally filtered by a WHERE condition.
MINUS: The MINUS operator returns rows from the first query that are not present in the second query.
Second SELECT: Retrieves the same columns from table_name2, optionally filtered by a WHERE condition.
Output: The result includes rows from table_name1 that do not exist in table_name2. Duplicates are removed from the final output.

Tables:

Table 1

Name	Address	Age	Grade
Harsh	delhi	20	A
Gaurav	jaipur	21	B
Pratik	mumbai	21	A
Dhanraj	kolkata	22	B

Table 2

Name	Age	Phone	Grade
Akash	20	XXXXXXXXXX	A
Dheeraj	21	XXXXXXXXXX	B
Vaibhav	21	XXXXXXXXXX	A
Dhanraj	22	XXXXXXXXXX	B

Queries:

SELECT NAME, AGE, GRADE
FROM Table1
MINUS
SELECT NAME, AGE, GRADE
FROM Table2;

Output:

Name	Age	Grade
Harsh	20	A
Gaurav	21	B
Pratik	21	A

In this query:

Returns only the rows that are unique to Table1.
If the last row in both tables is the same, it is excluded from the result. Thus, only the first three rows from Table1 will be returned.

Note:
The MINUS operator is supported in Oracle, but not supported in SQL Server or PostgreSQL.
For those databases, use the EXCEPT operator as an alternative.

SQL | MINUS Operator

Explore