查询A表B表同时存在记录， A表存在而B表不存在记录， B表存在而A表不存在记录

1. 问题

查询A表B表同时存在记录， A表存在而B表不存在记录， B表存在而A表不存在记录

如上图， 其中：

A区域是old表有， new表没有的集合;
C区域是new表有， old表没有的集合;
B区域是old表与new表共有的集合。

Q:已知old表与new表的表结构相同， 求A, B, C集合。

2. 场景模拟解决

下面有两张表分别为old表与new表， 表结构完全相同， 如下：

/*Table structure for table `new` */

DROP TABLE IF EXISTS `new`;

CREATE TABLE `new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `new` */

insert  into `new`(`id`,`name`,`age`) values (1,'刘备',11),(2,'关羽',12),(3,'张飞',13),(4,'赵云',1),(5,'诸葛亮',24);

/*Table structure for table `old` */

DROP TABLE IF EXISTS `old`;

CREATE TABLE `old` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `old` */

insert  into `old`(`id`,`name`,`age`) values (1,'赵云',1),(2,'刘备',11),(3,'诸葛亮',14),(4,'张飞',23);

old表有记录：

id  name    age
1   赵云    1
2   刘备    11
3   诸葛亮  14
4   张飞    23

new表有记录：

id  name    age
1   刘备    11
2   关羽    12
3   张飞    13
4   赵云    1
5   诸葛亮  24

3. sql代码解决

字段name， age相同的记录视为相同记录

/* old表和new表共有的记录 */
SELECT t1.name, t1.age FROM `old` t1, `new` t2 WHERE IFNULL(t1.name,'') = IFNULL(t2.name,'') AND IFNULL(t1.age,'') = IFNULL(t2.age,'');



/* 结果 */
name    age
刘备    11
赵云    1

/* old表有， new表没有的记录 */
SELECT t1.name, t1.age FROM `old` t1 WHERE NOT EXISTS (SELECT 1 FROM `new` t2 WHERE IFNULL(t1.name,'') = IFNULL(t2.name,'') AND IFNULL(t1.age,'') = IFNULL(t2.age,''));

/* 结果 */
name    age
诸葛亮  14
张飞    23

/* new表有， old表没有的记录 */
SELECT t1.name, t1.age FROM `new` t1 WHERE NOT EXISTS (SELECT 1 FROM `old` t2 WHERE IFNULL(t1.name,'') = IFNULL(t2.name,'') AND IFNULL(t1.age,'') = IFNULL(t2.age,''));

或：

SELECT t1.name, t1.age FROM `new` t1 WHERE (SELECT COUNT(1) AS num FROM `old` t2 WHERE IFNULL(t1.name,'') = IFNULL(t2.name,'') AND IFNULL(t1.age,'') = IFNULL(t2.age,'')) = 0;



/* 结果 */
name    age
关羽    12
张飞    13
诸葛亮  24

OK， Enjoy it!