MySQL 多表查询

 employees 表

departments 表

locations 表

job_grades 表 

---

两表连接，将 employees 表的 department_id 与 departments 表的 department_id 相关联

SELECT employee_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
# 或
SELECT employee_id, department_name
FROM employees JOIN departments
on employees.department_id = departments.department_id;

若查询字段在多个表中都出现，则需指明该字段所在的表，如 department_id 在 SELECT 中需写为 employees. department_id 或 departments.department_id，或者用表的别名代替表名（若给表起了别名，在 SELECT 和 WHERE 中必须使用别名，相当于别名覆盖了原表名） 

SELECT e.employee_id, e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
# 或
SELECT e.employee_id, e.last_name, d.department_name, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

---

等值连接和非等值连接

上述为等值连接

非等值连接

SELECT last_name, salary, garde_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

---

自连接和非自连接

上述为非自连接

自连接，表中自我引用，如 employees 表中 manager_id 相当于对 employee_id 的引用

SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

---

内连接和外连接

上述均为内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表不匹配的行以外，还查询到左表或右表中不匹配的行

外连接的分类：左外连接（还查询左表不匹配的行）、右外连接、满外连接

左外连接

SQL92语法（MySQL不支持）

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

SQL99语法，可以查询到 emplees 表中 department_id 为NULL的数据，即查询到所有员工的 department_name

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

右外连接

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

满外连接

MySQL不支持 FULL OUTER JOIN 

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

UNION 操作符

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

UNION ALL 返回两个查询的结果的并集，不去除重复记录

在实际使用中能用 UNION ALL 就不使用 UNION，因此满外连接使用上图①④进行 UNION ALL 

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

---

SQL99新特性

自然连接 NATURAL JOIN

可以理解为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;
# 等同于
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;

 USING

替换等值连接的 =，如上述查询可以写为

SELECT employee_id, last_name, department_name
FROM employees e JOIN departments d
USING (department_id, manager_id);