MySQL 的并集、交集和差集
table1 如下:
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 5 | d |
| 6 | e |
+----+------+
table2 如下:
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 5 | d |
+----+------+
两个表的并集使用如下语句:
select * from table1 union select * from table2
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 5 | d |
| 6 | e |
| 2 | b |
| 3 | c |
+----+------+
两个表的交集使用如下语句:
select table1.id,table2.name from table1 left join table2 using (id) where table2.id is not null
+----+------+
| id | name |
+----+------+
| 4 | c |
| 6 | e |
+----+------+
表1和表2的交集使用如下语句:
select table1.id,table2.name from table1 left join table2 using (id) where table2.id is null
+----+------+
| id | name |
+----+------+
| 4 | c |
| 6 | e |
+----+------+
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 5 | d |
| 6 | e |
+----+------+
table2 如下:
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 5 | d |
+----+------+
两个表的并集使用如下语句:
select * from table1 union select * from table2
+----+------+
| id | name |
+----+------+
| 1 | a |
| 4 | c |
| 5 | d |
| 6 | e |
| 2 | b |
| 3 | c |
+----+------+
两个表的交集使用如下语句:
select table1.id,table2.name from table1 left join table2 using (id) where table2.id is not null
+----+------+
| id | name |
+----+------+
| 4 | c |
| 6 | e |
+----+------+
表1和表2的交集使用如下语句:
select table1.id,table2.name from table1 left join table2 using (id) where table2.id is null
+----+------+
| id | name |
+----+------+
| 4 | c |
| 6 | e |
+----+------+
转载于:https://blog.51cto.com/zhengfj/289181