MySQL MINUS

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.

Syntax

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:

• The number and order of columns in all the SELECT statements must be the same.
• The data types of the corresponding columns in both SELECT statements must be the same or convertible.

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

Since MySQL does not provide support for MINUS operator. However, we can use a LEFT JOIN clause to simulate this operator.

We can use the following syntax to simulate the MINUS operator:

Example

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:

Output:

After executing the above query, we should get the output as below image: