MySql学习笔记-多表查询2-内外连接

内连接与外连接

课件均来源于b站尚硅谷，详细请移步b站
所有笔记链接

内连接：合并有同一列的多个表的行，结果集只包含表中相匹配的行

#下面就是MySQL92内连接：结果集就是相匹配的行
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id


#MySQL99的内连接，JOIN连接表，ON实现连接的关系
SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id		#连接关系

外连接：合并具有同一列的两个以上的表的行，结果集中除了相匹配的行之外，还查询到了左、右表中不匹配WHERE中条件的行

外连接分类：左外连接、右外连接、满外连接（左右都有）

---

实验中表的关系：

例题：

查询 所有的员工的last_name,department_name信息

分析：看到查询的是所有的员工并且是多表查询就注意要查询的可能是外连接了

先解释一下实验环境：来源于尚硅谷的课件其中employees表中有一个员工是没有department_id的，其余的员工（106名）都有,因此这就是外连接

哪一个表中的数据比较的少，那么我们就希望能够给他填充上使得两张表的表长度是一样的

#SQL92的语法（MySQL不支持）
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
#SQL99的语法
#左外连接
SELECT last_name, department_name
FROM employees e LEFT OUTER JOIN departments d	#OUTER 和 INNER都是可以省略的
ON e.department_id = d.department_id;

---

来源于尚硅谷课件

UNION的使用

使用UNION，顾名思义就是将集合联合起来，但是还是区分UNION和UNION ALL

其中UNION是对于两个集合取交集并且去除掉重复的记录

UNION ALL就是纯粹的取交集，不会去除掉重复的部分

UNION：

UNION ALL

如果在多表查询中，两种方式都能够得到正确的结果的话，建议使用UNION ALL，避免了去重，提高效率

7种SQL JOINS的实现

中间的图：内连接

SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id		#连接关系

左上面的图：左外连接

SELECT employee_id, department_name
FROM employees e LEFT OUTER JOIN departments d	#OUTER 和 INNER都是可以省略的
ON e.department_id = d.department_id;

右上面的图：右外连接

SELECT employee_id, department_name
FROM employees e RIGHT OUTER JOIN departments d	#OUTER 和 INNER都是可以省略的
ON e.department_id = d.department_id;

左中图：在左外连接的基础上面去掉中间的部分

在本样例中，中间的就是department_id不是NULL的，那我们在确定左外连接的基础上面用WHERE进行一次过滤，只索引department_id为NULL的就是了

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

右中图，同上，是右外连接去除掉中间重复的部分

SELECT employee_id, last_name, department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL

左下图，满外连接

MysQL竟然不支持FULL JOIN 😦

1.左上图 和 右中图 UNION ALL(不会去重，效率比较高)

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  #两表分开写，中间UNION ALL

SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

右下的图：左外的 union 右外的

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