MySQL：多表查询（一）

概述

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

笛卡尔积错误

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

• 错误：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;

• 原因：
  这样写的结果就是对每一个员工都匹配一遍部门（笛卡尔积的错误）
  所有表中的所有行互相连接
  为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

• 加入连接条件后，查询语法：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

在多表查询中，表名过长可以给每个表名起一个别名。

注意：如果起了别名后，在SELECT和WHERE中使用到表名时必须使用别名； 在表中有相同列时，在列名之前加上表名前缀。

#案例：查询员工的姓名及其部门名称
SELECT t1.last_name, t2.department_name
FROM employees t1, departments t2
WHERE t1.department_id = t2.department_id;

等值连接和非等值连接

• 多个表中有相同列时，必须在列名之前加上表名前缀。
• 在不同表中具有相同列名的列可以用 表名 加以区分。
• 使用别名可以简化查询，列名前使用表名前缀可以提高查询效率。

对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

• 等值连接

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

• 非等值连接

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

自连接和非自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

• 自连接
  举例：查询员工id，员工姓名，管理者id，管理者姓名

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

内连接和外连接

• 内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行（内连接 A∩B）(INNER可以省略)
  

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

• 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。（OUTER可以省略）
• 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  

SELECT employee_id,last_name,department_name
FROM employees e LEFT OUTER 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`;

• 满外连接(MySQL不支持)
  

按理来说满外连接使用FULL OUTER JOIN … ON … ,但是MySQL不支持SQL99的FULL

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