MINUS

语法说明

MINUS 比较两个查询的结果，并返回第一个查询中不是由第二个查询输出的不同行。

语法结构

SELECT column_list_1 FROM table_1
MINUS
SELECT columns_list_2 FROM table_2;

示例

• 示例 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 |
+------+

• 示例 2

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 |
+------+------+