MySQL连接查询补充与三表连查

前言

MySQL多表联查是指在一个查询语句中同时查询多个表，并根据表之间的关联条件进行数据的匹配和筛选。通过多表联查，我们可以获取到更丰富的数据信息，从而满足复杂的查询需求。先前了解了三种简单的连接查询方式，这里将进一步介绍更复杂的连接查询。

关于内连接、左连接、右连接介绍请参考：MySQL高级SQL语言常用查询与连接查询-CSDN博客

目录

一、外连接的其他使用

1. full [outer] join（全连接）

2. left join + where right.table is null

3. right join + where left.table is null

二、三表连查 

1. 概述

2. 格式 

3. 示例

---

一、外连接的其他使用

场景：

mysql> select * from class1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
mysql> select * from class2;
+------+------+
| id   | age  |
+------+------+
|    1 | 10   |
|    2 | 20   |
|    4 | 30   |
+------+------+

1. full [outer] join（全连接）

返回左表和右表中所有的行，如果某个表中的行在另一个表中没有匹配的行，则用NULL填充。它用于获取两个表中的所有数据，无论是否有匹配的行。

格式：

select 列名 from 表1 full join 表2 on 表1.列名 = 表2.列名;

示例：full join 在 mysql 支持的较少，可以用 left join union right join 模拟代替

mysql> select a.id aid,a.name,b.id bid,b.age from class1 a left join class2 b on a.id=b.id union select a.id aid,a.name,b.id bid,b.age from class1 a right join class2 b on a.id=b.id;
+------+----------+------+------+
| aid  | name     | bid  | age  |
+------+----------+------+------+
|    1 | zhangsan |    1 | 10   |
|    2 | lisi     |    2 | 20   |
|    3 | wangwu   | NULL | NULL |
| NULL | NULL     |    4 | 30   |
+------+----------+------+------+

2. left join + where right.table is null

表示对左连接后的结果进行过滤，只保留那些右表中没有匹配记录的行。即得到左表全部数据，去掉满足右表的共同区域。

格式：

select a.* from 表1 a left join 表2 b on a.相同字段 = b.相同字段 where b.相同字段 is null;

示例：

mysql> select a.* from class1 a left join class2 b  on a.id = b.id where b.id is null;
+------+--------+
| id   | name   |
+------+--------+
|    3 | wangwu |
+------+--------+

3. right join + where left.table is null

表示对右连接后的结果进行过滤，只保留那些左表中没有匹配记录的行。即得到右表全部数据，去掉满足左表的共同区域。

格式：

select b.* from 表1 a right join 表2 b on a.相同字段 = b.相同字段 where a.相同字段 is null;

示例：

mysql> select b.* from class1 a right join class2 b  on a.id = b.id where a.id is null;
+------+------+
| id   | age  |
+------+------+
|    4 | 30   |
+------+------+

二、三表连查 

1. 概述

在 MySQL 中进行三表连接查询是一种常见的数据库操作，用于检索多个表中相关数据的方法。三表连接允许我们同时连接三个表，通过指定连接条件来获取符合条件的数据。这种查询通常涉及使用 join 子句来连接表，常见的三种连接方式包括内连接、左连接和右连接。

2. 格式 

select 列名
from 表1
join 表2 om 表1.列名 = 表2.列名
join 表3 on 表2.列名 = 表3.列名
where 条件;

3. 示例

环境准备：

mysql> select * from t1;
+------+-------+
| id   | score |
+------+-------+
|    1 | 100   |
|    3 | 150   |
|    5 | 120   |
+------+-------+
mysql> select * from t2;
+------+----------+---------+
| id   | name     | classid |
+------+----------+---------+
|    1 | zhangsan |     111 |
|    2 | lisi     |     222 |
|    5 | wangwu   |     555 |
+------+----------+---------+
mysql> select * from t3;
+---------+-----------+
| classid | class     |
+---------+-----------+
|     111 | yinianji  |
|     444 | ernianji  |
|     555 | sannianji |
+---------+-----------+

目标：内连接查询，查找字段相同值，最后显示name、score、class信息

mysql> select b.name,a.score,c.class from t1 a inner join t2 b on a.id=b.id inner join t3 c on b.cllassid=c.classid;
+----------+-------+-----------+
| name     | score | class     |
+----------+-------+-----------+
| zhangsan | 100   | yinianji  |
| wangwu   | 120   | sannianji |
+----------+-------+-----------+

 左连接查询：

mysql> select b.name,a.score,c.class from t1 a left join t2 b on a.id=b.id left join t3 c on b.classid=c.classid;
+----------+-------+-----------+
| name     | score | class     |
+----------+-------+-----------+
| zhangsan | 100   | yinianji  |
| wangwu   | 120   | sannianji |
| NULL     | 150   | NULL      |
+----------+-------+-----------+

右连接查询： 

mysql> select b.name,a.score,c.class from t1 a right join t2 b on a.id=b.id right join t3 c on b.cllassid=c.classid;
+----------+-------+-----------+
| name     | score | class     |
+----------+-------+-----------+
| zhangsan | 100   | yinianji  |
| wangwu   | 120   | sannianji |
| NULL     | NULL  | ernianji  |
+----------+-------+-----------+