1.多表查询的分类:

       角度1: 等值链接 VS 非等值连接
       
       角度2: 自链接 VS 非自连接
       
       角度3: 内链接 VS 外连接

1.1 等值链接 VS 非等值连接

等值连接:

from employees e, departments d
where e.department_id = e.department_id;

非等值连接

from employees e, departments d
where e.salary >= d.lowest_sal and e.salary <= d.highest_sal;

1.2自链接 VS 非自连接

自连接(同一个表连接)

FROM employees e, employees e2
WHERE e.manager_id = e2.employee_id;

外连接(不同的表连接)

, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

1.3内链接 VS 外连接

内连接(INNER JOIN)的实现--->两张表的交集

语法：

FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他字句;

外连接(OUTER JOIN)的实现

#中图：内连接 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
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