SQL入门之第十一讲——OUTER JOIN 外连接

外连接是左外连接（LEFT OUTER JOIN），右外连接（RIGHT OUTER JOIN），全外连接（FULL OUTER JOIN）的统称。

一般情况下直接忽视掉OUTER就可以，直接统称：

  • 左外连接为：左连接（LEFT JOIN）
  • 右外连接为：右连接（RIGHT JOIN）
  • 全外连接为：全连接（FULL JOIN）

左连接和右连接，在结果中除了交集，还包含左/右表中的全部记录。
具体查看图片会比较清楚点：
在这里插入图片描述

1. 左连接（LEFT JOIN）

  • 定义：左连接就是将JOIN前面的表中所有记录都展示出来。

实例1：将学生表和教师表通过教师编号Tid连接起来

select * 
from teachers as t
left join students as s
on t.Tid = s.Tid

执行结果：

  • 从结果上看，将教师表中全部的教师都查询出来了，对于学生表中对应不上的，以Null填充

在这里插入图片描述

2 . 右连接（RIGHT JOIN）

  • 定义：右连接就是将JOIN后面的表中所有记录都展示出来。跟左连接是对应互通的。

例如，还是将第一个实例中的teachers 表 和 students 表 进行互换，再将LEFT JOIN 改成RIGHT JOIN，可以看到执行的结果，输出的记录是一样的。

实例2：将学生表和教师表通过教师编号Tid连接起来

select * 
from students as s
right join teachers as t
on s.Tid = t.Tid

执行结果：
在这里插入图片描述
可以看到所输出的记录是一样的，唯一的区别是在右连接中：

  • students 表中的（Sid、Sname）在前面
  • Teachers表中的（Tid、Tname）在后面

3 . 全连接（FULL JOIN）

  • 全连接的结果集结合了右连接和左连接的结果集
  • 在MySQL中是不支持FULL JOIN，在实际的业务中，全连接用的比较少，大多数是使用右连接和左连接。

4 . 多表外连接

  • 定义：多表外连接与多表内连接类似，都是可以对多个表（3个及以上）进行外连接查询。
  • 当合并的表较多时，同时用LEFT JOIN 和 RIGHT JOIN 会比较混乱，不容易理解，因此，我们最好只是用JOIN 和 LEFT JOIN，不用 RIGHT JOIN。

基本语法：

select <字段名>
from <表a>
left join <表b>
on a.<字段名> = b.<字段名> 
left join <表c>
on a.<字段名> = c.<字段名>

实例3：教师表同时连接学生表和课程表，通过教师编号Tid连接起来，老师需要全部显示出来

select * 
from teachers as t
left join students as s
on t.Tid = s.Tid
left join suject as u
on t.Tid = u.Tid

执行结果：
在这里插入图片描述

5 . 自外连接

  • 定义：自外连接是指，通过LEFT JOIN 将表自己连接起来

例如：有一个users表，求出次日留存用户数：某日活跃的用户在次日仍旧活跃的用户数
在这里插入图片描述

select 
a.log_date as '日期',
count(DISTINCT b.user_id) as '次日留存用户数'
from users a 
left join users b
on a.user_id = b.user_id
and DATEDIFF(b.log_date,a.log_date)=1
GROUP BY a.log_date;

实例解析：这个用到了聚合函数，日期函数，GROUP BY语句这些。

  • DATEDIFF函数是用来计算两个日期间隔天数的
  • 聚合函数COUNT是返回匹配条件的行数。
  • GROUP BY语句是对最后的结果集进行分组，通过与聚合函数搭配使用
  • DISTINCT 这个是用来进行去重