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)