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

末蓝、 2023-10-01 09:26 20阅读 0赞

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]

> 写在开头：跟随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操作符**

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]

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

**UNION ALL操作符**

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 2]  
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 可以归纳为以下图形：**

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 3]

#### 代码实现 ####

**以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表如图所示：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 4]

#例题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 可以查到所有的字段。

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: https://img-blog.csdnimg.cn/4b03efa364294c2db8482e0c5600041a.jpg?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 1]: https://img-blog.csdnimg.cn/d4f9c78f819147f5afda7d1bc0aa276e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 2]: https://img-blog.csdnimg.cn/4af9dfb672564365bc08de3f63419285.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 3]: https://img-blog.csdnimg.cn/84b43c8c7f9a424eb5b93222776d94c4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center 4]: https://img-blog.csdnimg.cn/9f2bbb9887914229ace2741de3681ded.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5oiR5piv5LiA5qO15Y235b-D6I-c,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center