五、 7种SQL JOINS的实现
5.1 代码实现
#中图：内连接 A∩B SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id`;
#左上图：左外连接 SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id`;
#右上图：右外连接 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;
#左中图：A - 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
#右中图：B-A∩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
#左下图：满外连接 # 左中图 + 右上图 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`;
#右下图 #左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - 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
5.2 语法格式小结
- 左中图
#实现A - A∩B select 字段列表 from A表 left join B表 on 关联条件 where 从表关联字段 is null and 等其他子句;
- 右中图
#实现B - A∩B select 字段列表 from A表 right join B表 on 关联条件 where 从表关联字段 is null and 等其他子句;
- 左下图
#实现查询结果是A∪B #用左外的A，union 右外的B select 字段列表 from A表 left join B表 on 关联条件 where 等其他子句 union select 字段列表 from A表 right join B表 on 关联条件 where 等其他子句;
- 右下图
#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B） #使用左外的 (A - A∩B) union 右外的（B - A∩B） select 字段列表 from A表 left join B表 on 关联条件 where 从表关联字段 is null and 等其他子句 union select 字段列表 from A表 right join B表 on 关联条件 where 从表关联字段 is null and 等其他子句
六、 SQL99语法新特性
6.1 自然连接
SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
在SQL92标准中：
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.`department_id` = d.`department_id` AND e.`manager_id` = d.`manager_id`;
在 SQL99 中你可以写成：
SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;
