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 theEXCEPT
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.