Skip to content

MINUS

Description

MINUS compares the result of two queries and returns the different rows in the first query that are not output by the second query.

Syntax

SELECT column_list_1 FROM table_1
MINUS
SELECT columns_list_2 FROM table_2;

Examples

  • Example 1
CREATE TABLE t1 (id INT PRIMARY KEY);
CREATE TABLE t2 (id INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);

mysql> SELECT id FROM t1 MINUS SELECT id FROM t2;
+------+
| id   |
+------+
|    1 |
+------+
  • Example 2

```sql drop table if exists t1; drop table if exists t2; create table t1 (a smallint, b bigint, c int); insert into t1 values (1,2,3); insert into t1 values (1,2,3); insert into t1 values (3,4,5); insert into t1 values (4,5,6); insert into t1 values (4,5,6); insert into t1 values (1,1,2); create table t2 (a smallint, b bigint, c int); insert into t2 values (1,2,3); insert into t2 values (3,4,5); insert into t2 values (1,2,1);

mysql> select * from t1 minus select * from t2; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 1 | 2 | | 4 | 5 | 6 | +------+------+------+

mysql> select a, b from t1 minus select b, c from t2; +------+------+ | a | b | +------+------+ | 3 | 4 | | 1 | 1 | | 1 | 2 | +------+------+ ```