mysql
  1. mysql-minus

Minus Operator (MySQL Misc)

The MINUS operator is a subtraction operator used in MySQL to compare two result sets. In this tutorial, we'll discuss how to use the MINUS operator in MySQL.

Syntax

The MINUS operator in MySQL is used as follows:

SELECT column1, column2, ...
FROM table1
MINUS
SELECT column1, column2, ...
FROM table2;

The operator subtracts the result set generated by the second SELECT statement from the first one, returning the rows that are in the first set but not in the second.

Example

Let's consider the following two tables:

Table1
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
| 1  | Alice | 90    |
| 2  | Bob   | 85    |
| 3  | John  | 75    |
+----+-------+-------+

Table2
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
| 1  | Alice | 90    |
| 3  | John  | 75    |
+----+-------+-------+

We can use the MINUS operator to find the entries in Table1 that are not present in Table2 based on the id column:

SELECT id, name, score
FROM Table1
MINUS
SELECT id, name, score
FROM Table2;

The resulting output would be:

+----+------+-------+
| id | name | score |
+----+------+-------+
| 2  | Bob  | 85    |
+----+------+-------+

Explanation

In the example above, we used the MINUS operator to compare the result generated by the two SELECT statements. We subtracted the second result set from the first one, resulting in a new result set that contains only the rows that are in the first set but not in the second.

Use

The MINUS operator is useful when you need to compare the results of two SELECT statements and find the differences between them.

Important Points

  • The MINUS operator is specific to MySQL and is not part of the SQL standard.
  • The MINUS operator is similar to the EXCEPT operator in other databases such as PostgreSQL and Oracle.
  • The columns selected in the two SELECT statements must have the same data types and the same order.
  • The MINUS operator only returns distinct rows. If there are multiple identical rows in the result set, they will be returned once.

Summary

In this tutorial, we discussed how to use the MINUS operator in MySQL to compare the result sets generated by two SELECT statements and find the differences between them. We covered the syntax, example, explanation, use, and important points of the MINUS operator in MySQL.

Published on: