神奇的 SQL 之团结的力量 → JOIN

前言

　　开心一刻

　 　　闺蜜家暴富，买了一栋大别野，喊我去吃饭，菜挺丰盛的，筷子有些不给力，银筷子，好重，我说换个竹子的，闺蜜说，这种银筷子我家总共才五双，只有贵宾才能用~我咬着牙享受着贵宾待遇，终于，在第三次夹虾排滑落盘子时，我爆发了：去它喵的贵宾，我要虾排……不是……我要竹筷子！

连接

　　简单来说，就是将其他表中的列添加过来，进行"添加列"的运算，如下图所示。

　　为什么需要进行"添加列"的操作 了？ 因为我们在设计数据库的时候，往往需要满足范式（具体满足范式几，无法一概而论，这里不做细究），会导致我们某个需求的全部列分散在不同的表中，所以为了满足需求，我们需要将某些表的列进行连接。我们来看个简单例子，假如我们有两张表（t_user，t_login_log）：

drop table if exists t_user;
create table t_user (
  id int(11) unsigned not null auto_increment comment '自增主键',
  user_name varchar(50) not null comment '用户名',
  sex tinyint(1) not null comment '性别, 1:男，0:女',
  age tinyint(3) unsigned not null comment '年龄',
  phone_number varchar(11) not null default '' comment '电话号码',
  email varchar(50) not null default '' comment '电子邮箱',
  create_time datetime not null comment '创建时间',
  update_time datetime not null comment '更新时间',
  primary key (id)
) comment='用户表';

drop table if exists t_login_log;
create table t_login_log (
  id int(11) unsigned not null auto_increment comment '自增主键',
  user_name varchar(50) not null comment '用户名',
  ip varchar(15) not null comment '登录ip',
  client tinyint(1) not null comment '登录端, 1:android, 2:ios, 3:pc, 4:h5',
  create_time datetime not null comment '创建时间',
  primary key (id)
) comment='登录日志';

insert into t_user(user_name, sex, age, phone_number,email,create_time,update_time) values
('bruce lee', 1, 32, '15174480987', '[e-mail]', now(), now()),
('*', 1, 65, '15174481234', '[e-mail]', now(), now()),
('jet li', 1, 56, '15174481245', '[e-mail]', now(), now()),
('jack ma', 1, 55, '15174481256', '[e-mail]', now(), now()),
('pony', 1, 48, '15174481278', '[e-mail]', now(), now()),
('robin li', 1, 51, '15174481290', '[e-mail]', now(), now());

insert into t_login_log(user_name, ip, client, create_time) values
('*', '10.53.56.78',2, '2019-10-12 12:23:45'),
('*', '10.53.56.78',2, '2019-10-12 22:23:45'),
('jet li', '10.53.56.12',1, '2018-08-12 22:23:45'),
('jet li', '10.53.56.12',1, '2019-10-19 10:23:45'),
('jack ma', '198.11.132.198',2, '2018-05-12 22:23:45'),
('jack ma', '198.11.132.198',2, '2018-11-11 22:23:45'),
('jack ma', '198.11.132.198',2, '2019-06-18 22:23:45'),
('robin li', '220.181.38.148',3, '2019-10-21 09:45:56'),
('robin li', '220.181.38.148',3, '2019-10-26 22:23:45'),
('pony', '104.69.160.60',4, '2019-10-12 10:23:45'),
('pony', '104.69.160.60',4, '2019-10-15 20:23:45');

　　如果我们需要展示如下列表（需求：展示用户列表，并显示其最近登录时间、最近登录 ip），那么就需要 t_user 和 t_login_log 连表查了

　　连接的类型有很多种，细分如下图

交叉连接

　　讲交叉连接之前了，我们先来看看笛卡尔积，假设我们两个集合，集合a={a, b}，集合b={0, 1, 2}，则a与b的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}，表示为axb，也就是集合a中的任一元素与集合b的每个元素组合后的新集合则为a与b的笛卡尔积（axb）。数学上的笛卡尔积反映到数据库中就是交叉连接（cross join），结合上述的案例如下：

select * from t_user cross join t_login_log;

-- 与 cross join 得到的结果相同
-- 过时的写法，不符合 sql标准，能读懂就好，不推荐使用
select * from t_user, t_login_log;

　　t_user 中有 6 条记录， t_login_log 中有 11 条记录，t_user cross join t_login_log 的结果是 66（ 6 乘以 11） 条记录

　　交叉连接就是对两张表中的全部记录进行交叉组合，因此其结果是两张表的乘积，这也是为什么交叉连接无法使用内连接或外连接中所使用的 on 子句的原因。交叉连接基本不会应用到实际业务之中，原因有两个，一是其结果没有实用价值，而是结果行数太多，需要花费大量的运算时间和硬件资源。虽说交叉连接的实际使用场景几乎没有，但还是有它的理论价值的，交叉连接是其他所有连接运算的基础，内连接是交叉连接的一部分，其结果是交叉连接的一部分（子集），外连接有点特殊，其结果包含交叉连接之外的内容；更多详情，我们接着往下看。

内连接

　　只返回两张表匹配的记录，就叫内连接，直观的表现就是关键字：inner join ... on，on 表示两张表连接所使用的列（连接键）；而内连接中又属等值连接最常用

　　等值连接

　　　　简单点来说，就是连接键相等

-- 等值连接
select * from t_user tu inner join t_login_log ttl on tu.user_name = ttl.user_name;

-- inner join 可以简写成 join
select * from t_user tu join t_login_log ttl on tu.user_name = ttl.user_name;

-- 不加连接键, 结果与 cross join 一样
select * from t_user tu inner join t_login_log ttl

　　　　等值连接的结果中，每一条记录的连接键的列的值是想等的，如上图中的 user_name 和 user_name1（为了区别于第一个user_name，数据库系统自动取的别名，我们可以显示的指定）

　　不等值连接

　　　　连接键的比较谓词除了 = 之外的所有情况，比如 >、<、<>（!=）；不等值连接使用场景比较少，反正我在实际工作中几乎没用到过

select * from t_user tu inner join t_login_log ttl on tu.user_name <> ttl.user_name;
select * from t_user tu inner join t_login_log ttl on tu.user_name > ttl.user_name;

　　自然连接

　　　　不需要指定连接条件，数据库系统会自动用相同的字段作为连接键，直观的表现就是关键字：natural join，natural left join、natural right join；

　　　　连接键不直观，需要去看两张表中相同的字段有哪些；对于自然连接，了解即可，不推荐使用，反正我工作这么久，一次都没用过。

外连接

　　外连接的使用方式与内连接一样，也是通过 on 使用连接键将两张表连接，从结果中获取我们想要的数据，但是返回的结果与内连接有区别，具体我们往下看

　　左连接

　　　　返回匹配的记录，以及左表多余的记录，关键字：left join（left outer join 的简写）

select * from t_user tu left outer join t_login_log ttl on tu.user_name = ttl.user_name;
-- left join 是 left outer join 的简写
select * from t_user tu left join t_login_log ttl on tu.user_name = ttl.user_name;

　　　　上图中，前 11 条记录是匹配的记录，而第 12 条是不匹配、左表的记录

　　右连接

　　　　返回匹配的记录，以及表 b 多余的记录，关键字：right join（right outer join 的简写）

select * from t_login_log ttl right outer join t_user tu on tu.user_name = ttl.user_name;
-- right join 是 right outer join 的简写
select * from t_login_log ttl right join t_user tu on tu.user_name = ttl.user_name;

　　　　由于我们习惯了从左往右（阅读方式、写作方式），因此在实际项目中，基本上用的都是左连接

　　全连接

　　　　返回匹配的记录，以及左表和右表各自的多余记录，关键字：full join （full outer join 的简写）

select * from t_user tu full outer join t_login_log ttl on tu.user_name = ttl.user_name;
-- full join 是 full outer join 的简写
select * from t_user tu full join t_login_log ttl on tu.user_name = ttl.user_name;

　　　　注意：mysql 不支持 全连接，我们可以通过 左连接、右连接之后，再 union 来实现全连接

自连接

　　一张表，自己连接自己，简单点来理解就是，左表、右表是同一张表；连接方式可以是内连接、也可以是外连接

　　更多详情大家可以去看：

需求：展示用户列表，并显示最近登录时间、最近登录 ip

　　对于此需求，大家会如何来写这个 sql ？ 也许大家很容易想到左连接，如下所示

select * from t_user tu left join t_login_log ttl on tu.user_name = ttl.user_name;

　　可结果如下：

 

 

　　显示的是每个用户的所有登录日志，不是我们想要的结果；原因是 t_user 中的一条记录在 t_login_log 对应的记录有多种情况：0 条对应、1 条对应、多条对应，那这个 sql 要怎么写呢，方式有多种，不局限于如下实现

-- 1、连接配合子查询，注意 bruce lee 从未登陆过
select tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
from t_user tu left join t_login_log tll on tu.user_name = tll.user_name
where tll.id = (select max(id) from t_login_log where user_name = tu.user_name) or tll.user_name is null;

-- 2、t_login_log分组统计出各个用户的最近一次登录信息后，再与 t_user 联表
select tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip 
from t_user tu left join (
    select tb.* from(
        select user_name, max(id) id from t_login_log group by user_name
    ) ta left join t_login_log tb on ta.id = tb.id
) tll on tu.user_name = tll.user_name;

　　具体的实现还得结合具体的业务和需求来实现，那样才能写出高效的 sql；另外结合执行计划来建立合适的索引。总之，没有一成不变的、通用的高效 sql，结合具体的业务才能写出最合适的 sql。

总结

　　1、连接的描述方式

　　　　常用的，描述如下

　　　　维恩图描述有他的优势，但它不好表示交叉连接，同时容易让人误解成 sql 中的集合操作；这里推荐另外一种，我觉得描述的更准确

　　　　cross join

 　　　　常用 join

 　　　　上图中，颜色表示匹配关系，颜色相同表示匹配。返回结果中，如果另一张表没有匹配的记录，则用 null 填充， 在上图中则表示为空白。

　　2、连接中 on 指定连接键，连接键可以指定多个，而 where  还是平时的作用，用来指定过滤条件；不推荐将连接键放于 where 后；

　　3、实际工作中，用的最多的是 左连接 和 等值连接，其他的用的特别少

参考

　　《sql基础教程》

　　《sql进阶教程》