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

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

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

*A）内连接*：join，inner join

*B）外连接*：left join，left outer join，right join，right outer join，union

*C）交叉连接*：cross join

案例表：

t_users:

t_department:

第一种：内连接 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

三种写法：
select * from student s, color c where s.stuname = c.stuname;
select * from student s inner join color c on s.stuname = c.stuname;
select * from student s join color c on s.stuname = c.stuname;

特点：
内连接也叫然连接，只有两个表相匹配的才能在结果集中出现。返回的结果集选取两个表中所匹配的数据，舍弃不匹配的数据 。

第二种：外连接 left join，left outer join ，right join， right outer join， union

1）左连接 LEFT JOIN

就是求两个表A表和B表的 交集外加左表剩下的数据 。依旧从笛卡尔积的角度讲，就是先从笛卡尔积中挑出ON子句条件成立的记录，然后加上 左表A表中剩余的记录 （见最后三条）。

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

2）右连接 RIGHT JOIN

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

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

3）外连接 FULL OUTER JOIN

外连接就是求两个表 A和B集合的并集 。从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。

另外MySQL不支持OUTER JOIN，但是我们可以对左连接和右连接的结果做 UNION 操作来实现。

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

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

4) 笛卡尔乘积cross join

即不加任何条件，达到 M*N 的结果集。
以下两种查询结果一样。

select * from student s cross join color c
select * from student s , color c

注意：如果cross join加上where on s.stuname = c.stuname条件，会产生跟自连接一样的结果（ cross join 后加上 on 报错 ）：
加上条件，产生跟自连接一样的结果。
select * from student s cross join color c where s.stuname = c.stuname;
自连接结果集的cross join连接结果

总结
所有的join连接，都可以加上类似where a.id='1000’的条件，达到同样的效果。 因为on不能做这种判断 ，只能是
除了 cross join不可以加on 外，其它join连接都必须加上on关键字，后都可加where条件。
虽然都可以加where条件，但是他们只在标准连接的结果集上查找where条件。比如左外连接的结果没有class的三班，所以如果加 where class.id='C003’虽然在表中有，但在左连接结果集中没有，所以查询后，是没有记录的。
A表有100条数据，B表有80条数据，left join on 1=1,where 1=1的结果是：应该是笛卡尔积
A表有100条数据，B表有80条数据， left join on 1=2,where 1=1的结果是：应该是A表的值
测试：
select * from student s left join color c on 1=1 where 1=1

自然连接和内连接区别

Mysql之自然连接 ：

自然连接和内连接是不等价的； 当两张表中出现相同的列名时，自然连接会自动合并列值，而内连接不会；
自然连接不用写条件就默认等值连接，

而内连接不指明条件的时候就默认笛卡尔积连接

寄语：

谢谢支持！