MySQL（4）多表查询

引言：为什么需要多表的查询？

A：提高效率，多线进行。

高内聚、低耦合。

一、多表查询的条件

1、错误的多表查询：

SELECT employee_id,department_name

FROM employees,departments;

SELECT employee_id,department_name

FROM employees CROSS JION departments;

每个员工与每个部门都进行一次匹配，出现笛卡尔积的错误。

错误原因：缺少了多表的连接条件。

解决方案：用WHERE关键字加入连接条件

2、若查询语句中出现多个表中都存在的字段，必须指明此字段所在的表。

3、可以给表起别名，在SELECT和WHERE中使用。

SELECT department_name,employee_id,departments.deps_id

FROM departments deps,employees emps

WHERE emps.department_id = deps.department_id;

但是，如果为表起了别名，则不能再使用原名。否则报错。

4、如果有n个表实现多表的查询，则至少有n-1个连接条件

二、多表查询的分类

（1）等值连接与非等值连接

等值连接即为：连接条件为相等运算的语句。

非等值连接：连接条件中不使用等号。

可以使用BETWEEN…AND、>=、<=等。

例如：

SELECT e.salary,e.last_name

FROM employees e,job_grades j

WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

（2）自连接与非自连接

自连接：

使用同一张表，通过取别名的方式进行SELECT的区分，使用WHERE写明查找的条件。

SELECT emp.employee_id,emp.last_name,mag.employee_id,mag.last_name

FROM employees emp,employees mag

WHERE emp.manager_id = mag.employee_id;

（3）内连接与外连接

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

理解为；合并两个以上的表中满足WHERE条件的行

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

可以理解为：除了内连接的表内容，还查询了两张表中内连接内容以外的内容。

外连接的分类；左外连接、右外连接、满外连接。

注意，查询所有的、不同的表中的数据，使用外连接。

 

MySQL不支持SQL92的外连接写法。

SQL99中使用JOIN … ON 实现外连接的写法。如果加入多张表，则依次使用JOIN ON进行连接。

SQL99中使用JION..ON的方式实现多表的查询

SQL99实现内连接：

SELECT employee_id,department_name,city

FROM employees e JOIN departments d

ON e.department_id = d.department_id

JOIN locations l

ON d.location_id = l.location_id;

 

SQL99实现外连接：

左外连接：

SELECT last_name,department_name

FROM employees e LEFT OUTER JOIN departments d

ON e.employee_id = d.department_id;

右外连接：

SELECT last_name,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id;

满外连接：

SELECT last_name,department_name

FROM employees e FULL JOIN departments d

ON e.department_id = d.department_id;

关于满外连接，MySQL不支持，Oracle支持。

OUTER可以省略。

UION关键字的使用：合并查询结果

SELECT……FROM……

UNION

SELECT……FROM……

UNION ALL操作符：在满外连接的情况下，加上两表的交集，对两表重复的部分不去重

三、SQL99的七种JOIN操作

1、左外连接

2、满足左外连接的条件并且右表为空

举例理解：

SELECT  employee_id,department_name

FROM employees e LEFT JOIN departments d

ON e.department_id = d.department_id

WHERE d.department_id IS NULL;

3、内连接

4、右外连接

5、满足右外连接的条件并且左表为空

SELECT employee_id,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id

WHERE e.employee_id IS NULL;

6、不去重的UNION ALL

取一个左外连接和去掉交集的右表。

或者取一个右外连接和去掉交集的左表。

SELECT  employee_id,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,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id;

7、去重的UNION

可视作2、5相连接

SELECT  employee_id,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,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id

WHERE e.employee_id IS NULL;

NATURAL JOIN与USING

SQL99语法的新特性1：自然连接

NATURAL JOIN：自动查询两张连接表中所有相同的字段，然后进行等值连接

但不够灵活。

新特性2：USING

USING可以替换等值条件。

当两张表中左等值的字段同名时可以采用。

SELECT employee_id,department_name

FROM employees e RIGHT JOIN departments d

USIING (department_id);