用小栗子帮助深入理解sql四种连接方式

1.sql四种连接

不同的SQL JOIN类型：

• inner join: 如果表中至少一个匹配，则返回行
• left join: 即使右表没有匹配，也会从左表返回所有的行
• right join: 即使左表中没有匹配，也从右表返回所有的行
• full jion: 只要其中一个表中存在匹配，则返回行

题目：四种常用连接的好例子

• 假设您有两个表，每个表只有一个列，表数据如下

A表：

A
1
2
3
4

B表：

B
3
4
5
6

注释:（1，2）是A表唯一的，（3，4）是公共的，并且（5，6）是B表独有

1.INNER JOIN 与 JOIN 是相同的；

2.在使用 join 时，on 和 where 条件的区别如下：

• 1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。
• 2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

1.1 内连接

定义：内连接是a表的所有行交上b表的所有行得出的结果集

select * from a INNER JOIN b on a.a = b.b; # 其实a,b也可以是同一个表的别名，即对一个表复制后通过条件进行自连接
select a.*, b.*  from a,b where a.a = b.b;
 
a | b
--+--
3 | 3
4 | 4

1.2 左外连接

定义：左外连接是A表的所有行匹配上B表得出的结果集

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);
 
a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

4.3 右外连接

定义：右外连接是B表的所有行匹配上A表得出的结果集

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;
 
a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

1.4 全连接

定义：全连接是A表的所有行并上B表的所有行得出的结果集

select * from a FULL OUTER JOIN b on a.a = b.b;
 
 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

1.5 内连接的特殊情况：自连接

定义：通过取别名的方式把一个表当成两个表来使用（特殊子连接）

如例一：查询所有员工和他领导的姓名
select e.ename 员工，p.ename 上级领导
from emp e inner join emp p on e.mgr=p.empno;

注释：自连接也是内连接的一种，是比较特殊的内连接，运用的是自己一张表构造的自连接，这样的目的是减少数据的冗余。