MySQL MINUS Operation – Quick Overview

In this tutorial, we will see what is the MINUS operator and how to use it in the query using a simple example. It is one of the three set operators – UNION, INTERSECT and MINUS.

MySQL Doesn’t provide the MINUS Operator. We will perform the MINUS operation using MySQL Join clause.

Also read: Introduction To MySQL Full-Text Search

What is the MINUS Operator?

The MINUS operator is very similar to the minus operation in real life. We perform a minus operation on two numbers and return whatever is left from the first number.

Similarly, the MINUS operator returns the number of records that are in the first table but not in the second table.

In more detailed words, the MINUS operator will compare the results of two queries and returns the rows from the result of the first query that are not present in the result of the second query.

Syntax of the MINUS Operator

Following is the syntax of the MINUS operator in SQL.

SELECT col_names 
FROM table_name1
MINUS 
SELECT col_names 
FROM table_name2;Code language: SQL (Structured Query Language) (sql)

Note that,

The column number of the first table and second table must be the same and of compatible type with each other. Else, it will give an error.

Also, the above syntax is of the SQL and not of MySQL as MySQL does not support the MINUS operator.

MINUS Operation Using JOIN

Here, we will see the example of the MINUS operator using a graphical representation first.

Let’s suppose we have two tables, id1 and id2 which consist of only the ids of a few members. Note that, few ids are present in both tables.

The above table looks something like this in vein diagram representation-

Now let’s create tables and insert values into them.

Note that, MySQL does not support the MINUS operator. However, we can perform the same operation using the JOIN clause.

CREATE TABLE id1(
id INT
);

CREATE TABLE id2)
id INT
);

INSERT INTO id1 VALUES(1),(2),(3),(4);

INSERT INTO id2 VALUES(3),(4),(5),(6);Code language: SQL (Structured Query Language) (sql)

SELECT * FROM id1;
SELECT * FROM id12;Code language: SQL (Structured Query Language) (sql)

Let’s write a query now to perform the minus operation.

SELECT id1.id FROM id1 LEFT JOIN id2 ON id1.id=id2.id
WHERE id2.id IS NULL;Code language: SQL (Structured Query Language) (sql)

Here, we have used the LEFT JOIN to join two tables. The ids which are present in the table ‘id1’ but not in ‘id2’ are shown as result.

As you can see, id 1 and 2 are not present in table ‘id2’.

Conclusion

In this tutorial, we have learned what is the MINUS operator and how it works. We have seen that MySQL doesn’t support the MINUS operator. However, we can emulate minus operator using the LEFT JOIN clause.