有的数据库不支持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