Mysql 多表连接查询 inner join 和 outer join 的使用

时间:2022-09-23 13:02:43

JOIN的含义就如英文单词“join”一样,连接两张表,大致分为内连接,外连接,右连接,左连接,自然连接。这里描述先甩出一张用烂了的图,然后插入测试数据。

Mysql 多表连接查询 inner join 和 outer join 的使用

Mysql 多表连接查询 inner join 和 outer join 的使用

 

首先先列举本篇用到的分类(内连接,外连接,交叉连接)和连接方法(如下):

A)内连接:join,inner join

B)外连接:left join,left outer join,right join,right outer join,union

C)交叉连接:cross join

案例表:

t_users:

Mysql 多表连接查询 inner join 和 outer join 的使用

t_department:

Mysql 多表连接查询 inner join 和 outer join 的使用

第一种:内连接 inner join

内连接INNER JOIN是最常用的连接操作。从数学的角度讲就是求两个表的交集,从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录。

有INNER JOIN,WHERE(等值连接),STRAIGHT_JOIN,JOIN(省略INNER)四种写法。

select d.id, d.department, t.name from t_users t
inner join t_department d on d.id = t.department_id

Mysql 多表连接查询 inner join 和 outer join 的使用

结果说明;只去表A表和B表共同有的on 条件部分数据;

第二种:外连接 left join,left outer join ,right join, right outer join, union

1)左连接LEFT JOIN的含义就是求两个表A表和B表的交集外加左表剩下的数据。依旧从笛卡尔积的角度讲,就是先从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表A表中剩余的记录(见最后三条)。

Mysql 多表连接查询 inner join 和 outer join 的使用

Mysql 多表连接查询 inner join 和 outer join 的使用where b.id is null

 

 

select   t.name, t.department_id, d.department from t_users t
left join t_department d on d.id = t.department_id

Mysql 多表连接查询 inner join 和 outer join 的使用

Mysql 多表连接查询 inner join 和 outer join 的使用

 

2)右连接 RIGHT JOIN

同理右连接RIGHT JOIN就是求两个表A和B表的交集外加右表B剩下的数据。再次从笛卡尔积的角度描述,右连接就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上右表中剩余的记录(见最后一条)

Mysql 多表连接查询 inner join 和 outer join 的使用

 

Mysql 多表连接查询 inner join 和 outer join 的使用 where a.id is null

 

select   t.id userid,t.name, t.department_id, d.department from t_users t
right join t_department d on d.id = t.department_id

Mysql 多表连接查询 inner join 和 outer join 的使用

 3)外连接 FULL OUTER JOIN

外连接就是求两个表A和B集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录,然后加上左表中剩余的记录,最后加上右表中剩余的记录。另外MySQL不支持OUTER JOIN,但是我们可以对左连接和右连接的结果做 UNION 操作来实现。

情景一:A和B的并集

Mysql 多表连接查询 inner join 和 outer join 的使用

 

select   t.id userid,t.name, t.department_id, d.department from t_users t
left join t_department d on d.id = t.department_id
UNION
select   t.id userid,t.name, t.department_id, d.department from t_users t
right join t_department d on d.id = t.department_id

Mysql 多表连接查询 inner join 和 outer join 的使用

情景二:A和B中不满足某个条件的记录

Mysql 多表连接查询 inner join 和 outer join 的使用

如:即不在A表user里面;也不在B表department里面的数据;条件 is null

select   t.id userid,t.name, t.department_id, d.department from t_users t
left join t_department d on d.id = t.department_id
where d.id is null
UNION
select   t.id userid,t.name, t.department_id, d.department from t_users t
right join t_department d on d.id = t.department_id
where t.department_id is null

Mysql 多表连接查询 inner join 和 outer join 的使用