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 这个是用来进行去重