The MINUS operator is a kind of SET operation in SQL which also includes INTERSECT, UNION, and UNION ALL. The MINUS operator returns the unique element from the first table/set, which is not found in the second table/set. In other words, it will compare the results of two queries and produces the resultant row from the result set obtained by the first query and not found in the result set obtained by the second query.
The following are the syntax that illustrates the use of a MINUS operator:
NOTE: MySQL does not provide support for the MINUS operator. This article shows you how to simulate the MINUS query in MySQL using the JOIN clause.
The following are the rules for a statement that uses the MINUS operator:
We can understand the MINUS operator with the following visual representation. Here this operator returns the distinct/unique values from the result obtained by the first SELECT statement and not found in the result obtained by the second SELECT statement:
Simulation of MySQL MINUS Operator
We can use the following syntax to simulate the MINUS operator:
Let us first creates two tables with the following structure for the demonstration:
Table1 structure and data:
Table2 structure and data:
Now, we will execute the below query with the help of the LEFT JOIN clause. It gives the same result as the MINUS operator:
After executing the above query, we should get the output as below image: