前言：
虽然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 | 少林 | 少林寺 | +------+--------+------+--------+--------+------+----------+---------+
