mysql连表查询在一个表中不在另一个表中的数据 连表查询 mysql

博主最近在数据库执行了类似这样的一条SQL

Update `user` a left join user_info b on a.id = b.user_id and a.id >2 and a.id < 20 set a.user_name = b.user_name;

聪明的你可能会发现这条 SQL 是很有问题的，在这里我想记录一下这次踩的坑，咋们来谈谈如何正确的连表

首先，连表有四种连接方式， left join 、right join 、inner join (join)， full outer join（mysql没有） 下面我用图集以及说明来表示这四种的区别（以下四张图片来源于网上）：

内连接，根据 on 后面的条件进行关联表查询，只有两表均满足条件时才返回对应的数据

 

左连接，包含左表所有数据以及右表中符合条件的数据

 

右连接，包含右表所有条件以及左表中符合条件的数据

 

全连接，包含左右表所有数据

 

用图不容易说明，我们用下实际的例子来验证一下，假设有这么两张表，为了你重现方便，我将对应的 SQL 语句放到这里，其他无用字段直接忽略：

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_info` (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(64) NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

并插入几条记录：

insert into user(`user_name`) values ('a');
insert into user(`user_name`) values ('b');
insert into user(`user_name`) values ('c');
insert into user(`user_name`) values ('d');
insert into user(`user_name`) values ('e');

insert into user_info(`user_id`,`user_name`)values(1,'a');
insert into user_info(`user_id`,`user_name`)values(2,'b');
insert into user_info(`user_id`,`user_name`)values(3,'c');
insert into user_info(`user_id`,`user_name`)values(7,'f');
insert into user_info(`user_id`,`user_name`)values(6,'h');

两张表的数据：

user 表

 

 

 

user_info 表

在 user 表中， id 为用户的自增id属性，在 user_info 表中，user_id 对应的是 user 表的 id ，因此连表时有个条件就是 on user.id = user_info.user_id,

在此我们分别用四种连接进行查询

首先是左查询：

select * from user a left join user_info b on a.id=b.user_id;

查询的结果是：

 

为了方便我把上面的图搬下来

 

我们可看到，查询出来的记录确实包含的 user 表的五条记录，以及包含着user_info 表符合 on 条件的三条记录，跟图示结果是完全一致的，其他不符合条件的右表的记录均为null

然后我们再看看 内连接，因为右连接和左连接的原理相同，故不赘述

select * from user a inner join user_info b on a.id=b.user_id;

查询到的结果是：

 

 

也很明显，只有交集部分的才能被查出，即user_id 为 1,2,3的三行记录

 

注：MySQL是不支持外连接查询，因此使用 FULL OUTER JOIN 时 在MySQL执行时会报错

 

了解了连接的基本原理，我们看看 连接时使用的 where 条件，放的不对会引起什么问题，可以看看下面的几条SQL的区别：

SELECT * FROM `user` a left join `user_info` b ON a.id = b.user_id and a.id > 2;

 

SELECT * FROM `user` a left join `user_info` b ON a.id = b.user_id where a.id > 2;

 

SELECT * FROM `user` a join `user_info` b ON a.id = b.user_id and a.id > 2;

 

 

SELECT * FROM `user` a join `user_info` b ON a.id = b.user_id where a.id > 2 ;

 

我们来解释一下上面的查询结果，首先你要了解到，左连接的特性是查询的数据一定包含左表所有的记录，而且放在 on 后面的条件是会被当做连接条件使用，因此 on a.id = b.user_Id and a.id >2,即左表所有记录以及关联条件是 a.id = b.user_Id and a.id >2 的右表记录，之所以出现右表只有一个 3,c的记录，而其他的都是null的原因是：满足a.id >2 条件的 user 表只有 id 为 3,4,5 的满足，而4,5 在右表中没有对应的记录，所以也是null

如果我们把条件放在where 后面的话，即把连表查询到的数据再进行一次过滤，即根据 a.id = b.user_id 查询到的数据 再通过 a.id >2 进行条件查询过滤

因为内连接没有左（右）连接的特性，因此把 条件放在 on 里面和 where 后面 结果都是等效的

知道了这些，我们来聊一聊文章提的那条更新语句有什么问题，我先吧SQL放在下面方便阅读：

Update `user` a left join user_info b on a.id = b.user_id and a.id >2 and a.id < 20 set a.user_name = b.user_name;

首先很明显，我们 a.user_id <20 放在了 on 后面而不是where 后面，其次是我用的是左连接，所以会出现很大的问题

为了方便理解，我先把上面的更新语句转换成查询语句：

select * from `user` a left join user_info b on a.id = b.user_id and a.id >2 and a.id < 20;

 

出现这样的结果并不意外，首先左表会被全更新，更新的逻辑是用右表的user_name覆盖左表的user_name ,而右表符合条件 a.id = b.user_id and a.id >2 and a.id < 20的记录是十分少的，例如上面的例子，只有 3,c这一条符合，那么除了user_id 为3 的记录均会被更新为 null,我们可以看看实际执行后会怎么样

 

 

很明显，因为 user 表的 user_name 属性设置了 not null ，属性，因此 MySQL 将 set a=null 转成了 set a=' '；

其次affected rows 的结果是4，说明这个影响的条数是基于表的前一个一致性状态和下一个一致性状态的比较取得的，例如这样的语句 affected row 就是0

Update `user` a join user_info b on a.id = b.user_id set a.user_name = b.user_name where a.id=3 ;

 

这是因为 3 对应的 user_name 本来就是 c,所以这条语句本身的前后结果是一致的，所以 affected row 为0

这就是日常工作真实遇到的坑，希望对你们有所帮助