各种数据库的交、差、并的SQL实现

数据库的交（intersect）差（except）并（union），是两张表之间的操作，两张表的结构必须相同。

SQL Server和Postgres数据库，直接支持了上述的3个关键字：intersect，except，union。

-- ----------------------------
-- 创建表t1
-- ----------------------------
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  name varchar(30) NOT NULL,
  PRIMARY KEY (name)
) ;

-- ----------------------------
-- 往t2里添加3条数据
-- ----------------------------
INSERT INTO t1 VALUES ('张三');
INSERT INTO t1 VALUES ('李四');
INSERT INTO t1 VALUES ('王五');

-- ----------------------------
-- 创建表t2
-- ----------------------------
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
  name varchar(30) NOT NULL,
  PRIMARY KEY (name)
) ;

-- ----------------------------
-- 往t2里添加2条数据
-- ----------------------------
INSERT INTO t2 VALUES ('张三');
INSERT INTO t2 VALUES ('马六');

-- ----------------------------
-- 并运算（默认会去除重复条目）
-- ----------------------------
select * from t1 union select * from t2;
 name
------
 王五
 马六
 张三
 李四
(4 行记录)


-- ----------------------------
-- 并运算（不去除重复条目）
-- ----------------------------
select * from t1 union all select * from t2;
 name
------
 张三
 李四
 王五
 张三
 马六
(5 行记录)

-- ----------------------------
-- 交运算
-- ----------------------------
select * from t1 intersect select * from t2;
 name
------
 张三
(1 行记录)

-- ----------------------------
-- 差运算（t1-t2）
-- ----------------------------

select * from t1 except select * from t2;
 name
------
 王五
 李四
(2 行记录)


-- ----------------------------
-- 差运算（t2-t1）
-- ----------------------------
select * from t2 except select * from t1;
 name
------
 马六
(1 行记录)

但 MySQL却仅支持了并（Union）；交和差，可以通过并来实现。示例SQL代码如下：

-- ----------------------------
-- 创建表t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `name` varchar(30) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- 往t2里添加3条数据
-- ----------------------------
INSERT INTO `t1` VALUES ('张三');
INSERT INTO `t1` VALUES ('李四');
INSERT INTO `t1` VALUES ('王五');

-- ----------------------------
-- 创建表t2
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `name` varchar(30) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- 往t2里添加2条数据
-- ----------------------------
INSERT INTO `t2` VALUES ('张三');
INSERT INTO `t2` VALUES ('马六');

-- ----------------------------
-- 并运算（默认会去除重复条目）
-- ----------------------------
select * from t1 union select * from t2;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 马六   |
+--------+

-- ----------------------------
-- 并运算（不去除重复条目）
-- ----------------------------
select * from t1 union all select * from t2;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 张三   |
| 马六   |
+--------+

-- ----------------------------
-- 交运算（并后，取重复条目）
-- ----------------------------
select * from (select distinct * from t1 union all select distinct * from t2)temp group by name having count(*)>1;
+--------+
| name   |
+--------+
| 张三   |
+--------+

-- ----------------------------
-- 差运算（并后，取重复条目数为1者）；注意：这个差是(t1-t2)并上（(t2-t1)
-- ----------------------------
select * from (select distinct * from t1 union all select distinct * from t2)temp group by name having count(*)=1;
+--------+
| name   |
+--------+
| 李四   |
| 王五   |
| 马六   |
+--------+