SQL实现交，并，差操作

有的数据库不支持intersect，except，所以交集，和差集使用嵌套查询来做比较靠谱。

a表和b表具有完全一样的结构

 
mysql> desc a;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| tel   | varchar(11) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql> desc b;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| tel   | varchar(11) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

a表和b表中的数据不同，现在要查询a表和b表的电话号码的交，并，差集：

mysql> select * from a;
+----+------+
| id | tel  |
+----+------+
|  1 | 1    |
|  2 | 2    |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from b;
+----+------+
| id | tel  |
+----+------+
|  1 | 2    |
|  2 | 3    |
+----+------+
2 rows in set (0.01 sec)

交集

//联合查询
mysql> select a.tel from a,b where a.tel = b.tel;
+------+
| tel  |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

//也可以使用嵌套查询
mysql> select a.tel from a where a.tel in (select b.tel from b);
+------+
| tel  |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

//也可以
mysql> select a.tel from a where exists (select * from b where a.tel = b.tel);
+------+
| tel  |
+------+
| 2    |
+------+

//当然intersect有可能是不支持的
mysql> select a.tel from a intersect select b.tel from b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select b.tel from b' at line 

并集

//MySQL从4.0以后是支持union，union all的，其实实现union是非常复杂的
mysql> select a.tel from a union select b.tel from b;
+------+
| tel  |
+------+
| 1    |
| 2    |
| 3    |
+------+
3 rows in set (0.00 sec)

//

差集

except所代表的差集，是在a中出现但是不在b中出现的记录；

mysql> select a.tel from a where a.tel not in ( select b.tel from b);
+------+
| tel  |
+------+
| 1    |
+------+
1 row in set (0.00 sec)
//同样，except也不一定支持
mysql> select a.tel from a except select b.tel form b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select b.tel form b' at line 

转载于:https://www.cnblogs.com/fangpengchengbupter/p/9356070.html