MINUS

语法说明

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

语法结构

  1. SELECT column_list_1 FROM table_1
  2. MINUS
  3. SELECT columns_list_2 FROM table_2;

示例

  • 示例 1
  1. CREATE TABLE t1 (id INT PRIMARY KEY);
  2. CREATE TABLE t2 (id INT PRIMARY KEY);
  3. INSERT INTO t1 VALUES (1),(2),(3);
  4. INSERT INTO t2 VALUES (2),(3),(4);
  6. mysql> SELECT id FROM t1 MINUS SELECT id FROM t2;
  7. +------+
  8. | id   |
  9. +------+
  10. |    1 |
  11. +------+
  • 示例 2
  1. drop table if exists t1;
  2. drop table if exists t2;
  3. create table t1 (a smallint, b bigint, c int);
  4. insert into t1 values (1,2,3);
  5. insert into t1 values (1,2,3);
  6. insert into t1 values (3,4,5);
  7. insert into t1 values (4,5,6);
  8. insert into t1 values (4,5,6);
  9. insert into t1 values (1,1,2);
  10. create table t2 (a smallint, b bigint, c int);
  11. insert into t2 values (1,2,3);
  12. insert into t2 values (3,4,5);
  13. insert into t2 values (1,2,1);
  15. mysql> select * from t1 minus select * from t2;
  16. +------+------+------+
  17. | a    | b    | c    |
  18. +------+------+------+
  19. |    1 |    1 |    2 |
  20. |    4 |    5 |    6 |
  21. +------+------+------+
  23. mysql> select a, b from t1 minus select b, c from t2;
  24. +------+------+
  25. | a    | b    |
  26. +------+------+
  27. |    3 |    4 |
  28. |    1 |    1 |
  29. |    1 |    2 |
  30. +------+------+