内连接与外连接（全网最详细）

目录

前言

一、内连接

二、外连接

1.左外连接

2.右外连接

3.满外连接

三、UNION的使用

四、7种SQL JOINS的实现

---

前言

      在学习内连接与外连接之前，你不妨思考为什么要引入这两种连接方式，带着问题去学习，更有助于我们对知识的学习。

        其实在单表查询中，我们是不会接触到内外连接查询的，内外连接查询的方式只是针对我们对于多表的查询，只不过这种方式在实际应用中的方式会根据不同业务需求去使用不同的方式来查询多表。

---

一、内连接

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

语法：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

类似这样：

方式一：
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

方式二：
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id;

---

先看这样一段代码：

select  员工表.id,部门表.department

from 员工表，部门表

where 员工表.部门id=部门表.部门id；     

这种查询方式：

它会把所有的符合where条件的字段查询出来。听起来十分合理，但是有这样一种这样的情况：就是两张表的数据有的不存在某种关系。（例如：员工表中有的员工他没有部门）

 缺点：如果我们想要把不满足条件的数据也查询出来，内连接就做不到。

于是我们引入外连接。

二、外连接

查询多表时一般要求中出现：查询所有的数据时，就一定会用到外连接。（重点记忆）

外连接：

  两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

（mysql不支持sql92，所以我们在写的外连接的语法都是sql99的）

           

                            

1.左外连接

• 语法：

 
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

• 举例：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

---

2.右外连接

• 语法：

FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

• 举例：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

---

3.满外连接

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

由于mysql不支持FULL JOIN，于是我们需要用 LEFT JOIN UNION RIGHT join代替。

三、UNION的使用

• 语法：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

• 举例：

普通查询：
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

使用union关键字查询：
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;    （把这种数据查询出来取并集）

具体用法：

UNION：会执行去重操作
UNION ALL:不会执行去重操作
结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，
则尽量使用UNION ALL语句，以提高数据查询的效率。
 

四、7种SQL JOINS的实现

 

 中图、左上图与右上图我们已经解决过了，分别对应内连接、左外连接以及右外连接。

---

• 左中图

思路：把左外连接查询出来的数据进行条件筛选

#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;   （把数据进行剔除）

 

例子：

#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL

先进行查询  再把数据进行where筛选

---

• 右中图

思路：把右外连接查询出来的数据进行条件筛选

#实现B - A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

例子：

#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

把数据查询出来 再进行筛选

---

• 左下图 （满外连接）

思路：使用union关键字来把数据库中查询的左外连接与右外连接查询出来的数据进行合并

值得注意的是：

用union all 来合并两个表中数据效率会高一些。

#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

例子：

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

---

• 右下图

思路：将左中图与右中图的数据合并起来

#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

例子：

#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL