MySQL联合查询

内容引出

我们有一个博客网站，里面有：

• 很多用户（user 表）

• 很多文章（weibo 表）

• 很多评论（comment 表）

我们希望能展示：

“谁在哪篇文章上，发了什么评论”。

1. user 表：用户信息表

字段名	类型	说明
id	bigint	主键
username	varchar(50)	登录用户名
password	varchar(100)	登录密码
nickname	varchar(50)	显示昵称
created	timestamp	注册时间

---

2. weibo 表：微博内容表

字段名	类型	说明
id	bigint	主键
content	varchar(255)	微博内容
created	timestamp	发布时间
user_id	bigint	发布用户ID

---

3. comment 表：评论信息表

字段名	类型	说明
id	bigint	主键
content	varchar(255)	评论内容
created	timestamp	评论时间
user_id	bigint	评论者用户ID
weibo_id	bigint	被评论微博ID

如果只看到了微博表就看不到用户根据某个微博发的评论了，如果只看评论表就看不到，某个被评论发布的微博的具体信息了

select* from weibo;

你只能看到每一条微博的内容、发布时间、发布者 ID，但你完全看不到这条微博有哪些评论、是谁评论的、评论了什么

---

select* from comment;

你可以看到评论者 ID、被评论微博的 ID，但你并不知道这个微博的内容是什么，也不知道用户昵称，只能看到 ID。

---

那我们究竟如何实现最初的目标  “谁在哪篇文章上，发了什么评论”  呢？

如果你只是看“微博文章表”，你看不到微博的评论；
如果你只是看“评论表”，你看不到评论发布到那个微博的具体信息；

为了解决这个问题，我们需要把评论信息，微博信息还有用户信息联合在一起也就是把评论表、微博表、用户表的信息整合到一起，才能还原出业务全貌。

联合查询（Join Query）是关系型数据库中非常重要的一种查询方式。我们之所以需要联合查询，是因为数据库设计中存在“表与表之间的关联关系”，而联合查询就是为了获取多个表之间的关联数据。总之一句话，关联查询就是为了从多个表中获取信息合并在一起用。

多表联合查询时MYSQL内部是如何进⾏计算的

参与查询的所有表(user,weibo,comment)取笛卡尔积，结果集在临时表中

这将会是一个十分庞大的内容，所以业务中我们会尽量避免笛卡尔积。

观察哪些记录是有效数据，根据三个表的关联关系过滤掉⽆效数据

找到微博表中的id 然后再在评论表中找到weibo_id 如果这条评论的weibo_id和微博表中的id相同说明这条评论确实就是发布在相关的微博上面的。通过这个条件我们可以确定微博表和评论表的关系

如何确定是谁发布评论的呢？

道理相同，只需要在用户表中找到id，然后在评论表中找到user_id，如果相同，可以确定这条评论确实是相关用户发布的。

从上面分析可以推导出，3个表关联只需要2个连接条件，那么n个表关联就需要n-1个连接条件。

---

注意：

上面表的设计严格遵从了数据库范式，所以我们可以利用关联查询来进行查询，

因为在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询⼀个条数据的完整信息，就要从多个表中获取数据。

内连接

语法

select 字段 from 表1 别名1, 表2 别名2

where 连接条件 and 其他条件;

这里的连接条件遵从上面我们推导出来的连接条件的规律。

---

语法糖形式：

select 字段 from 表1 别名1

[inner] join 表2 别名2 on 连接条件

where 其他条件;

在这里的join 表 别名  on 连接条件

同样遵从连接条件的规律可以这样写：

select 字段 from 表1 别名1

[inner] join 表2 别名2 on 连接条件,

[inner] join 表3 别名3 on 连接条件,

.

.

.

[inner] join 表n 别名n on 连接条件

where 其他条件;

---

示例

示例 1：

查询所有评论的详细信息（评论者昵称 + 评论内容 + 微博内容）

select u.nickname,c.content,w.content
from comment c
join weibo w on c.weibo_id = w.id
join user u on c.user_id = u.id
order by c.created DESC;;

示例 2：

查询某条微博的所有评论（比如 weibo_id = 201）

select w.content , c.content
from weibo w
join comment c on c.weibo_id = w.id
where c.weibo_id = 201
order by c.created ASC;;

---

外连接

外连接（Outer Join）用于查询两个表中不完全匹配的数据。
它不仅返回匹配成功的行，还会保留一边表中没有匹配成功的行，未匹配的数据列补 NULL。

• 外连接分为左外连接、右外连接和全外连接三种类型，MySQL不⽀持全外连接。
• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。
• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。
• 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹

配的记录，则结果集中对应字段会显⽰为NULL。

---

左外连接

左外连接:以JOIN左侧表作为驱动表，将该表记录都查询出来，不满足连接条件时，来自右侧表的中的字段补NULL。

听起来很抽象，我们举一个小例子

比如某电商平台，有商品表和商品厂家表，在列举商品时希望把后缀的厂家信息也都列出来，

某电商平台有两个表：

• product：商品表

• manufacturer：厂商信息表

正常情况下，每一件商品都有一个厂商：

商品名	价格	厂商 ID
小米手机	300	1
小米手表	200	1
华为电视	600	2

---

厂商表：

厂商 ID	厂商名
1	小米
2	华为

有一些“山寨货”也偷偷上线了，比如：

菠萝手机，大米手表，鸭蛋电视

商品名	价格	厂商 ID
小米手机	300	1
小米手表	200	1
华为电视	600	2
菠萝手机	100	null
大米手表	30	null
鸭蛋电视	200	null

这些商品没有合法厂商信息，manufacturer_id 是 NULL，或者压根在 manufacturer 表中找不到对应记录。这个时候我们该怎么查商品+厂商信息？使用 LEFT JOIN左连接

SELECT
p.name ,p.price ,m.name
FROM product p
LEFT JOIN manufacturer m ON p.manufacturer_id = m.id;

 查询结果

商品名	价格	厂商名
小米手机	300	小米
小米手表	200	小米
华为电视	600	华为
菠萝手机	100	NULL
大米手表	30	NULL
鸭蛋电视	200	NULL

 左连接（LEFT JOIN）就是“左边全保留，右边没匹配就补 NULL”；右连接反之。

---

右外连接

右外连接:以JOIN右侧表作为驱动表，将该表记录都查询出来，不满足连接条件时，来自左侧表的中的字段补NULL

如果你想保留的是右边的表，哪怕左边没对应数据，也要显示——那就要用 右外连接（RIGHT JOIN）。我们仍然使用商品，厂商的小案例来帮助理解。

前面我们讲了 LEFT JOIN（左外连接） 的例子：保留所有商品，哪怕没匹配的厂商信息，也要显示。

那 RIGHT JOIN 呢？它是反过来 —— 保留所有厂商，哪怕该厂商还没有生产任何商品，也要显示。

商品表如下

商品名	价格	厂商 ID
小米手机	300	1
小米电视	400	1
华为手机	300	2
菠萝手机	250	NULL

厂商表 

厂商 ID	厂商名
1	小米
2	华为
3	苹果
4	谷歌

 苹果和谷歌厂商虽然注册了厂商ID但是并没有在商品表中出现任何商品，这个时候我们想展示所有商品对应的厂商时，我们想看到的应该是所有厂商的商品情况，哪怕还没有商品！

所以反过来使用右外连接

SELECT
p.name ,p.price AS 价格,m.name 
FROM product p
RIGHT JOIN manufacturer m ON p.manufacturer_id = m.id;

查询结果

商品名	价格	厂商名
小米手机	300	小米
小米电视	400	小米
华为手机	300	华为
NULL	NULL	小米
NULL	NULL	谷歌

RIGHT JOIN 是 “右边优先” 的外连接，它让你可以查出“右表中存在但左表中没有匹配”的数据

全外连接

综合左外连接和右外连接，我们直接总结出全外连接（FULL OUTER JOIN）就是左外连接和右外连接的查询结合

小米手机	300	小米
小米手表	200	小米
华为电视	600	华为
菠萝手机	100	NULL
大米手表	30	NULL
鸭蛋电视	200	NULL
NULL	NULL	小米
NULL	NULL	谷歌

-- 注意：MySQL 本身不直接支持 FULL OUTER JOIN，但可以这样模拟：
SELECT ... FROM A
LEFT JOIN B ON ...
UNION
SELECT ... FROM A
RIGHT JOIN B ON ...

全外连接 = 左外连接 + 右外连接的并集，就是从两个方向都“不漏数据”。 

---

⾃连接

自连接就是一张表和自己做连接查询。
本质上就是对一张表进行笛卡尔积运算并加以条件筛选，让“同一个表中的两行”可以彼此比较或建立关系。

自连接的作用

• 实现 行与行的比较

• 查询 同一表中的父子关系、上下级关系

• 构建 树形结构

• 把 行变成列（需要配合聚合函数）

应用场景
电商中的分类树，就是用自连接设计

• 生活用品
• 家用电器
• 电脑
  • 电脑整机
    • 笔记本
      • …
      • …
    • 台式机
    • 游戏本
    • 一体机
    • 平板电脑
  • 电脑配件
  • 游戏设备
  • …
• 手机
• …

一家公司的员工组织结构，都是员工，但是员工又存在上下级关系。

id	name	parent_id
1	生活用品	NULL
2	家用电器	NULL
3	电脑	NULL
4	笔记本	3
5	台式机	3
6	一体机	3
7	平板电脑	3
8	游戏本	4
9	手机	NULL

你会发现：

• 所有分类都在同一张表里

• 某些分类的 parent_id 指向其他分类的 id 这就是自连接的典型场景

SELECT
  c1.name AS 分类名,
  c2.name AS 上级分类
FROM category c1
LEFT JOIN category c2 ON c1.parent_id = c2.id;

查询结果 

分类名	上级分类
电脑	NULL
生活用品	NULL
家用电器	NULL
手机	NULL
笔记本	电脑
游戏本	笔记本
台式机	电脑

自连接是实现同表数据之间相互比较、层级关系、树结构查询的强大工具，它让我们能用一张表表达出“行与行之间的逻辑