inner join&left join&right join的区别？

今天复习了一下多表查询，简单做个笔记。

下面这个图很清楚的描述了各连接方式的区别：    

举个例子来了解有两张表，student、score，字段如下：

    

一、inner join（内连接，等同于等值连接）

      1、inner join :

          select stu.sname as 姓名, s.subject as 科目, s.grade as 分数 from student stu inner join score s on stu.sid = s.sid;

      2、等值连接：

          select stu.sname as 姓名, s.subject as 科目, s.grade as 分数 from student stu, score s where stu.sid = s.sid;

效果是一样的：

   

二、left join（左连接：包含左子表的全部记录）

select stu.sname as 姓名, s.subject as 科目, s.grade as 分数 from student stu left join score s on stu.sid = s.sid;

三、right join（右连接：包含右子表的全部记录）

select stu.sname as 姓名, s.subject as 科目, s.grade as 分数 from student stu right join score s on stu.sid = s.sid;

四、full join（全连接：包含左右子表的全部记录）

select stu.sname as 姓名, s.subject as 科目, s.grade as 分数 from student stu full join score s on stu.sid = s.sid;

要注意的：在MySQL中并不支持全连接，但是我们可以使用union（会自动去除重复字段），将左右子表联合起来构造全连接，例如

select stu.sname as 姓名, s.subject as 科目, s.grade as 分数 from student stu left join score s on stu.sid = s.sid
union
select stu.sname as 姓名, s.subject as 科目, s.grade as 分数 from student stu right join score s on stu.sid = s.sid;

这个效果跟上面的full join是一样的。