一篇文章理解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关联AB关联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);
### 不同类型 SQL JOIN 的区别和使用场景 #### LEFT JOIN LEFT JOIN 返回左表中的所有记录,即使右表中没有匹配。如果右表中没有找到匹配项,则结果集会包含 NULL 值[^3]。 ```sql SELECT A.*, B.* FROM A LEFT JOIN B ON A.id = B.id; ``` 此查询将返回来自 `A` 表的所有行;对于那些在 `B` 表中有对应关系的数据也会被显示出来。当 `B` 表里不存在与之关联的信息时,在最终的结果集中这些字段会被填充成 NULL。 #### INNER JOIN INNER JOIN 创建两个表之间的交集——仅保留两表都有的数据部分。这意味着只有满足条件的组合才会出现在输出之中[^2]。 ```sql SELECT A.*, B.* FROM A INNER JOIN B ON A.id = B.id; ``` 这段代码只选取能够通过指定键相联接的部分条目作为输出的一部分,即同时存在于两张表格内的项目将会呈现于最终列表之上。 #### RIGHT JOIN RIGHT JOIN 是指获取右侧表内所有的记录加上左侧表中存在的任何匹配项。如果没有相应的配对存在的话,那么左边那侧就会用 NULL 来代替缺失的内容。 ```sql SELECT A.*, B.* FROM A RIGHT JOIN B ON A.id = B.id; ``` 上述语句执行之后得到的是 `B` 表全部的数据集合再加上有共同 id 属性的对象自 `A` 表提取出来的信息。一旦某个对象仅仅属于 `A` 而不在 `B` 当中出现过,其相应位置上则为空白(NULL)。 #### FULL JOIN FULL JOINLEFT JOINRIGHT JOIN 合并起来考虑,意味着无论哪一边都有可能成为主要来源,只要某一方含有符合条件的数据就可以加入到结果当中去。因此,这会产生最广泛的选择范围,除非双方都没有相对应之处才会有完全由 null 构成的新纪录[^1]。 ```sql SELECT A.*, B.* FROM A FULL OUTER JOIN B ON A.id = B.id; ``` 这条命令旨在收集尽可能多的相关资料,无论是从哪个角度出发都能确保不会遗漏任何一个潜在联系点。假如任意一端缺乏对方所定义的关键属性链接,则另一方仍然可以独立展示自己的成员,并且把缺少的那一面设为null表示未知状态。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值