INTERSECT

语法说明

INTERSECT 运算符是一个集合运算符仅返回两个查询或多个查询的不同行。

语法结构

  1. SELECT column_list FROM table_1
  2. INTERSECT
  3. SELECT column_list FROM table_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 intersect select * from t2;
  16. +------+------+------+
  17. | a    | b    | c    |
  18. +------+------+------+
  19. |    1 |    2 |    3 |
  20. |    3 |    4 |    5 |
  21. +------+------+------+
  22. 2 rows in set (0.01 sec)
  24. mysql> select a, b from t1 intersect select b, c from t2;
  25. +------+------+
  26. | a    | b    |
  27. +------+------+
  28. |    4 |    5 |
  29. +------+------+
  30. 1 row in set (0.01 sec)