MySQL多表连查之inner join、left join、right join

前言

这几年一直在it行业里摸爬滚打，一路走来，不少总结了一些python行业里的高频面试，看到大部分初入行的新鲜血液，还在为各样的面试题答案或收录有各种困难问题

于是乎，我自己开发了一款面试宝典，希望能帮到大家，也希望有更多的Python新人真正加入从事到这个行业里，让python火不只是停留在广告上。

微信小程序搜索：Python面试宝典

或可关注原创个人博客：https://lienze.tech

也可关注微信公众号，不定时发送各类有趣猎奇的技术文章：Python编程学习

连接

使用join连接可以使我们一次姓通过过滤条件获取到多张表内的联系结果

比如一个老师有很多学生，分别为老师表，学生表

在学生表中外键关联老师，如何在查询学生时，把其对应老师的信息也查询获取，就可以利用连接查询

---

表结构

• 老师表

-- auto-generated definition
create table Teacher
(
    id   int auto_increment,
    name varchar(255) null comment '老师名',
    constraint Teacher_id_uindex
        unique (id)
);

alter table Teacher
    add primary key (id);

• 学生表

学生表中teacher_id字段关联老师表id主键

-- auto-generated definition
create table Student
(
    id         int auto_increment,
    name       varchar(255) null,
    teacher_id int          null comment '老师id',
    constraint Student_id_uindex
        unique (id)
);

alter table Student
    add primary key (id);

• 测试数据们

+----+-----------+
| id | name      |
+----+-----------+
|  1 | 李老师    |
|  2 | 彭老师    |
+----+-----------+

+----+------------------+------------+
| id | name             | teacher_id |
+----+------------------+------------+
|  1 | 模拟小学生1      |          1 |
|  2 | 模拟小学生2      |          1 |
|  3 | 模拟小学生3      |       NULL |
|  4 | 模拟初中生1      |          2 |
|  5 | 模拟初中生2      |       NULL |
|  6 | 模拟初中生3      |          3 |
+----+------------------+------------+

部分学生具有老师，而部分学生目前暂无老师关联

INNER JOIN

INNER JOIN为内连接语句，如果存在匹配项，则返回匹配记录的多张表数据

---

通俗的解释，如果希望能获取到每个具有老师的学生及他老师的详细信息，就可以使用内连接

select * from Student
inner join Teacher on Teacher.id = Student.teacher_id;

这样可以将不包含老师的学生过滤在外，并且包含所关联老师的信息，内连接只能获取到必须存在连接关系的多表中数据

+----+------------------+------------+----+-----------+
| id | name             | teacher_id | id | name      |
+----+------------------+------------+----+-----------+
|  1 | 模拟小学生1      |          1 |  1 | 李老师    |
|  2 | 模拟小学生2      |          1 |  1 | 李老师    |
|  4 | 模拟初中生1      |          2 |  2 | 彭老师    |
+----+------------------+------------+----+-----------+

LEFT JOIN

左连接以左表数据为主，哪怕左表数据有不与右表关联的数据，也会将对应关联字段值置为空返回

select * from Student
left join Teacher on Teacher.id = Student.teacher_id;

+----+------------------+------------+------+-----------+
| id | name             | teacher_id | id   | name      |
+----+------------------+------------+------+-----------+
|  1 | 模拟小学生1      |          1 |    1 | 李老师      |
|  2 | 模拟小学生2      |          1 |    1 | 李老师      |
|  3 | 模拟小学生3      |       NULL | NULL | NULL       |
|  4 | 模拟初中生1      |          2 |    2 | 彭老师      |
|  5 | 模拟初中生2      |       NULL | NULL | NULL       |
|  6 | 模拟初中生3      |          3 | NULL | NULL       |
+----+------------------+------------+------+-----------+

RIGHT JOIN

以连接中右表数据为主

比如查询主表为老师，现在右表为学生表，部分学生不包含老师，但是右连接接的特性也会让这些学生出现在结果中

select * from Teacher
right join Student on Teacher.id = Student.teacher_id;

+------+----------+----+------------------+------------+
| id   | name     | id | name             | teacher_id |
+------+----------+---+------------------ +------------+
|    1 | 李老师    |  1 | 模拟小学生1       |          1 |
|    1 | 李老师    |  2 | 模拟小学生2       |          1 |
| NULL | NULL     |  3 | 模拟小学生3       |       NULL |
|    2 | 彭老师    |  4 | 模拟初中生1       |          2 |
| NULL | NULL     |  5 | 模拟初中生2       |       NULL |
| NULL | NULL     |  6 | 模拟初中生3       |          3 |
+------+-----------+----+------------------+------------+

其他练习

• 用户表

create table user
(
    uid            varchar(32)                          not null comment '用户uid
    phone          varchar(32)                          null comment '用户手机号
    constraint phone_UNIQUE
        unique (phone),
    constraint uid_UNIQUE
        unique (uid)
)
    comment '用户核心信息表';

alter table user
    add primary key (uid);

• 通讯录表

-- auto-generated definition
create table user_mailist
(
    id    int auto_increment
        primary key,
    name  varchar(45) null comment '通讯录手机号备注',
    phone varchar(45) null comment '通讯录手机号',
    uid   varchar(45) null comment '外键关联用户',
    constraint user_mailst_user_uid_fk
        foreign key (uid) references user (uid)
)
    comment '通讯录表';

• 关注表

create table user_follows
(
    uid        varchar(45) not null comment '当前用户id',
    followUid  varchar(45) not null comment '我关注的人的 id',
    followTime bigint      null comment '关注的时间（精确到秒）',
    constraint index1
        unique (uid, followUid)
)
    comment '用户关注信息表，uid和followUid唯一索引';

create index followUid_index
    on user_follows (followUid);

create index index2
    on user_follows (uid);

用户用户之间通过user_follows进行关联，并且每个用户都有自己的一系列多对一的通讯录用户

---

问: 请在数据库中查询，我通讯录中在平台存在的好友，并且我没有关注过的这些用户

答

SELECT user.uid, user.name, user.phone
FROM user WHERE user.phone in
(SELECT um.phone 
    FROM user 
    INNER JOIN user_mailist um on user.uid = um.uid
    WHERE user.uid = '1')
AND user.uid NOT in
(SELECT user.uid 
    FROM user
    INNER JOIN user_follows uf on user.uid = uf.uid
    WHERE uf.followUid = '1')
AND user.status = 0;