MySQL：UNION、UNION ALL以及7种SQL JOINS的实现

写在开头：跟随B站的康师傅学习mysql的笔记摘要和自我总结。

前言：

• 虽然MySQL不支持FULL JOIN(满外连接)，但是可以用 LEFT JOIN UNION RIGHT JOIN代替。

• 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

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

UNION操作符

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

UNION ALL操作符

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

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
#一个员工可能符合所有条件，所以要去重
SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

#由题目可知，该两者之间无任何关联，所以不用去重，UNION ALL 效率高
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

7种SQL JOINS的实现(重点！)

• 7种SQL JOINS 可以归纳为以下图形：

代码实现

以employees表和departments表为例：

#中图：内连接(A B 共有)
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#左上图：左外连接(A 的全集)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#右上图：右外连接(B 的全集)
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#左中图：(A 的独有)
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

#右中图：(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

#左下图：满外连接
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 的独有)
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

课后练习题加强：

t_dept表、t_emp表如图所示：

#例题1.所有有门派的人员信息 （ A、B两表共有） 
SELECT t1.* 
FROM t_dept t1 INNER JOIN t_emp t2
ON t1.`id` = t2.`id`;

#例题2.列出所有用户，并显示其机构信息 （A的全集） 
SELECT t1.* 
FROM t_emp t1 LEFT JOIN t_dept t2
ON t1.`id` = t2.`id`;

#例题3.列出所有门派 （B的全集） 
SELECT *
FROM t_dept ;

#例题4.所有不入门派的人员 （A的独有） 
SELECT t1.*
FROM t_emp t1 LEFT JOIN t_dept t2
ON t1.`id` = t2.`id`
WHERE t2.`id` IS NULL;

#例题5.所有没人入的门派 （B的独有）
SELECT t1.*
FROM t_dept t1 LEFT JOIN t_emp t2
ON t1.`id` = t2.`id`
WHERE t2.id IS NULL;

#例题6.列出所有人员和机构的对照关系(AB全有)
SELECT * 
FROM t_emp A  LEFT JOIN t_dept B
ON A.deptId = B.id 
UNION 
SELECT * 
FROM t_emp A  RIGHT JOIN t_dept B 
ON A.deptId = B.id

#查询结果：
+------+----------+------+--------+--------+------+----------+---------+
| id   | name     | age  | deptId | empno  | id   | deptName | address |
+------+----------+------+--------+--------+------+----------+---------+
|    1 | 风清扬   |   90 |      1 | 100001 |    1 | 华山     | 华山    |
|    2 | 岳不群   |   50 |      1 | 100002 |    1 | 华山     | 华山    |
|    3 | 令狐冲   |   24 |      1 | 100003 |    1 | 华山     | 华山    |
|    4 | 洪七公   |   70 |      2 | 100004 |    2 | 丐帮     | 洛阳    |
|    5 | 乔峰     |   35 |      2 | 100005 |    2 | 丐帮     | 洛阳    |
|    6 | 灭绝师太 |   70 |      3 | 100006 |    3 | 峨眉     | 峨眉山  |
|    7 | 周芷若   |   20 |      3 | 100007 |    3 | 峨眉     | 峨眉山  |
|    8 | 张三丰   |  100 |      4 | 100008 |    4 | 武当     | 武当山  |
|    9 | 张无忌   |   25 |      5 | 100009 |    5 | 明教     | 光明顶  |
|   10 | 韦小宝   |   18 |   NULL | 100010 | NULL | NULL     | NULL    |
| NULL | NULL     | NULL |   NULL |   NULL |    6 | 少林     | 少林寺  |
+------+----------+------+--------+--------+------+----------+---------+

#例题7.列出所有没入派的人员和没人入的门派(A的独有 + B的独有)
SELECT * 
FROM t_emp A  LEFT JOIN t_dept B 
ON A.deptId = B.id 
WHERE B.`id` IS NULL 
UNION ALL
SELECT * 
FROM t_emp A  RIGHT JOIN t_dept B 
ON A.deptId = B.id 
WHERE A.`deptId` IS NULL;

#查询结果；
+------+--------+------+--------+--------+------+----------+---------+
| id   | name   | age  | deptId | empno  | id   | deptName | address |
+------+--------+------+--------+--------+------+----------+---------+
|   10 | 韦小宝 |   18 |   NULL | 100010 | NULL | NULL     | NULL    |
| NULL | NULL   | NULL |   NULL |   NULL |    6 | 少林     | 少林寺  |
+------+--------+------+--------+--------+------+----------+---------+

爱在结尾：1、UNION ALL 效率比 UNION 高，尽量使用 UNION ALL。 2、若查询A表中独有的数据，查询条件的 is (not) null 对应的字段应该选择B表中为非空的字段。 3、left join (right join) 要理解其作用：在不加筛选条件下，用left join 可以查到所有的字段。