一篇解读SQL JOINS(MySQL版)

先上图，学会一图即可

a.演示图表

employees表

departments表

b.演示7种查询

提示：
一般要求你查询带所有的数据是，并且是多表连接，就可能用的SQL JOINS。
本篇文章，在from中employees表处于左边，departments处于右边(便于对比)

1.左外连接

#查询所有的员工的id,name,department_name
SELECT emp.`employee_id`, emp.`name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

结果如下：

我们能看出，有的员工他并没有部门，如果使用where department_id = dept.department_id`判断,就会只输出前4条查询结果，不符合题目要求。

2.右外连接

#查询所有的部门的部门和员工信息
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

结果如下：

有的部门可能刚成立，还没有员工，所以我们需要使用右外连接，如果使用where department_id = dept.department_id`判断,就会只输出前4条查询结果，少了Plans部门，不符合题目要求。

3.内连接

#查询所有部门的员工信息信息
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

结果如下：

内连接是A与B的交集，要求部门存在且部门有员工，此时会剔除department_id为NULL的老七信息。

4.左中图

#查询所有没有部门的员工
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

结果如下：

先进行左外连接，再利用WHERE dept.department_id IS NULL条件过滤employees 与 departments 的交集，就可以得到 employees 的独有！

5.右中图

#查询所有没有员工的部门
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

结果如下：

思路同左外连接一样，先求右外连接，再对二者的交集进行过滤，就可以得到departments 的独有。

6.满外连接

提示：
MySQL不支持full join语法
UNION操作符：
UNION 操作符返回两个查询的结果集的并集，去除重复记录
UNION ALL操作符：
UNION ALL操作符返回两个查询的结果集的并集。
对于两个结果集的重 复部分，不去重

#查询所有员工和部门的对应关系
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

结果如下：

满外连接可以用左外连接和右中图进行合并查询UNION ALL操作可以得到，为什么不使用UNION操作呢？因为UNION操作会进行去重，而明显我们的查询不需要去重操作，故使用UNION ALL操作效率高。

7.右下图

# 列出所有没部门的员工和没员工的部门
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL
UNION ALL
SELECT emp.`employee_id`, emp.`name`, dept.`department_id`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

结果如下：

右下图可由左中图和右中图进行合并查询UNION ALL操作获得。

c.总结

所以大家应当将SQL JOINS图牢记于心，并且记住其下的SQL模板，然后自己多去尝试一下，熟能生巧！特别是左外连接和右外连接，一定要明白你要查询的是左表的全部(对应left outer join)还是右表的全部，同时要学会求内连接，并以此为基础再学会另外4中连接操作