一篇文章理解inner join/left join/right join的细节

一篇文章理解inner join/left join/right join的细节

本文强烈建议自己动手实验。两表连接比较简单，可以直接跳到多表连接查看

结论

无论是双表还是多表关联，可以 通过分解步骤来化解复杂性。比如三张表，先考虑前两张表进行关联，得到的结果想象成临时表，再跟第三张表关联。连接的顺序对结果是会产生影响的！

一、两表

1、一对一

people(id,name)；id_card(id,people_id,card_no)。数据见附录。

例

--结论：查出3条数据。inner join必须两表的数据都能连接上才会展示
select * from people a inner join id_card b on a.id=b.people_id;

例

-- 结论：查出4条数据。以左表为准。左表的数据都会出现，若左表某行数据无右表数据关联，则右表所有字段补null
-- 注意：假设左表有4条数据，左连接的时候不是一定的4条数据，而是大于等于4,假设右表跟左表是多对一的，则左表会超过4条数据
select * from people a left join id_card b on a.id=b.people_id;

例

-- 结论：查出4条数据。跟上述左连接相反，以右边数据为准
select * from people a right join id_card b on a.id=b.people_id;

备注：mysql 没有 full join

2、一对多

master(id,name)；pet(id,name,master_id)。数据见附录。

例

-- 结论：查出2条，1条master记录和2条pet记录组合
select * from master a inner join pet b  on a.id=b.master_id;

例

-- 结论：展示3条记录，其中能连接上的展示了2条，不能连接的展示为1条。不能连接的右表展示为null
select * from master a left join pet b  on a.id=b.master_id;

注意：

• 为什么出现2条记录而不是1条？不是说 “以左表为准” 吗？
  • 答：“以左表为准” 并不能得出 “left join的数量等于左表”。反证法：如果查询结果是1条记录，那右表应该展示的哪条记录呢？所以肯定是展示2条的。

例

-- 结论：展示为3条记录，其中一条无法连接的，左边的表展示为null
select * from master a right join pet b  on a.id=b.master_id;

二、多表

多表比较复杂，常回让人产生困扰，让人困惑于连接的细节。例如3张表A、B、C，可能会产生如下困惑：

• B、C关联A 和 B关联A，C关联B两种情况是相同的吗？前面的情况有共同的基础的关联表A；但是后面是一环套一环关联的
• 连接的运算顺序是怎么样的？顺序不同结果会不同吗？
• 表和表之间可能存在多对一的情况

结论见开头

1、一对一

people(id,name)；id_card(id,people_id,card_no)；country(id,country,people_id)。数据见附录。

例

-- 结论：先前面2张表连表，得到3行记录，再和最后一张表连表，最终得到2行记录
select * from people a 
inner join id_card b on a.id=b.people_id
inner join country c on a.id=c.people_id

例

-- 结论：先前面2张表连表，得到4行记录，所有的people表记录都会出现。再和country表连接，最终得到4行记录
select * from people a 
left join id_card b on a.id=b.people_id
left join country c on a.id=c.people_id

例

-- 结论：第一步，前面2表得到临时表的结果；第二步，将先前的结果再一次运算

-- 1
select * from people a 
left join id_card b on a.id=b.people_id
inner join country c on a.id=c.people_id

-- 2
select * from people a 
left join id_card b on a.id=b.people_id
right join country c on a.id=c.people_id

一对多

master(id,name)；pet(id,name,master_id)；toy(id,toy_name,pet_id)。数据见附录。

例

-- 结论：按顺序进行连接
select * from master a
inner join pet b on a.id=b.master_id
inner join toy c on b.id=c.pet_id

例

-- 结论：按顺序进行连接
select * from master a
left join pet b on a.id=b.master_id
left join toy c on b.id=c.pet_id

附录

测试的表和数据

CREATE TABLE `country`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `country` varchar(255) NOT NULL,
  `people_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `country` VALUES (1, '仙剑1', 1);
INSERT INTO `country` VALUES (2, '仙剑2', 2);
INSERT INTO `country` VALUES (3, '仙剑-1', -1);


CREATE TABLE `id_card`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `card_no` varchar(50) NOT NULL,
  `people_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `id_card` VALUES (1, '441', 1);
INSERT INTO `id_card` VALUES (2, '442', 2);
INSERT INTO `id_card` VALUES (3, '443', 3);
INSERT INTO `id_card` VALUES (4, '-1', -1);


CREATE TABLE `master`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `master` VALUES (1, '爱宠人士');
INSERT INTO `master` VALUES (2, '不养宠物');


CREATE TABLE `people`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `people` VALUES (1, '玄霄');
INSERT INTO `people` VALUES (2, '天河');
INSERT INTO `people` VALUES (3, '韩菱纱');
INSERT INTO `people` VALUES (4, '柳梦璃');


CREATE TABLE `pet`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `master_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `pet` VALUES (1, '阿猫', 1);
INSERT INTO `pet` VALUES (2, '阿狗', 1);
INSERT INTO `pet` VALUES (3, '流浪猫狗', -1);


CREATE TABLE `toy`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `toy_name` varchar(255) NOT NULL,
  `pet_id` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `toy` VALUES (1, '阿猫的玩具：毛线', 1);
INSERT INTO `toy` VALUES (2, '阿猫的玩具：纸团', 1);
INSERT INTO `toy` VALUES (3, '没有主的玩具', -1);