MySQL-多表查询

多表查询

---

多表查询简介

笛卡尔乘积

1. 笛卡尔乘积的形成，当:

   • 一个连接条件被遗漏时

   • 一个连接条件不正确时

   • 在第一个表中的所有行被连接到第二个表的所有行时

2. 为了避免笛卡尔乘积的形成，在WHERE子句中应当总是包含正确的连接条件

笛卡尔乘积 ：

当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积(Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。你应该在 WHERE 子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。

多表查询分类

• sql92标准：内连接(等值连接 、非等值连接 、 自连接)。
• sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。

SQL92标准中的查询

等值连接

等值连接

为了确定一个雇员的部门名，需要比较 EMPLOYEES 表中的DEPARTMENT_ID 列与DEPARTMENTS 表中的 DEPARTMENT_ID列的值。在 EMPLOYEES 和DEPARTMENTS 表之间的关系是一个相等 (equijoin) 关系，即，两 个 表 中DEPARTMENT_ID 列的值必须相等。

等值连接特点：

1. 多表等值连接的结果为多表的交集部分；
2. n表连接，至少需要n-1个连接条件；
3. 多表不分主次，没有顺序要求；
4. 一般为表起别名，提高阅读性和性能；
5. 可以搭配排序、分组、筛选….等子句使用；

注意：

等值连接也被称为简单连接 (simple joins) 或内连接 (innerjoins)。

等值连接的使用

select 
employees.EMPLOYEE_ID,employees.LAST_NAME,
employees.DEPARTMENT_ID,
departments.DEPARTMENT_ID,departments.LOCATION_ID
from employees,departments
where employees.DEPARTMENT_ID = departments.DEPARTMENT_ID;

• SELECT 子句指定要返回的列名：

  − employee last name、employee number 和 departmentnumber，这些是EMPLOYEES 表中的列

  − department number、department name 和 location ID，这些是 DEPARTMENTS 表中的列

• FROM 子句指定数据库必须访问的两个表：

  − EMPLOYEES 表

  − DEPARTMENTS 表

• WHERE 子句指定表怎样被连接：

  EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列是两个表的同名列，它必须用表名做前缀以避免混淆。

增加搜索条件

使用AND操作符附加搜索条件

添加查询条件

除连接之外，可能还要求用 WHERE 子句在连接中限制一个或多个表中的行。

限制不能缺的列

限制不明确的列名

• 需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID列可能来自 DEPARTMENTS 表，也可能来自 EMPLOYEES 表，这种情况下需要添加表前缀来执行查询。
• 如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为MySQL服务器可以根据表前缀找到对应的列。
• 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如 SELECT子句或 ORDERBY 子句。

使用表别名

表别名定义原则

• 表别名不易过长，短一些更好。
• 表别名应该是有意义的。
• 表别名只对当前的 SELECT 语句有效。

多表连接

为了连接n个表，你最少需要n-1个连接条件。例如，为了连接3个表，最少需要两个连接

示例一：

查询雇员 King 所在的部门名称。

select 
d.department_name 
from employees e,departments d   
where e.EMPLOYEE_ID = d.department_id and e.last_name = 'King';

示例二：

显示每个雇员的 last name、departmentname 和 city。

SELECT 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;

非等值连接

一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES表 和JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个表之间的关系是EMPLOYEES 表中的 SALARY 列必须是JOB_GRADES 表的 LOWEST_SALARY 和HIGHEST_SALARY 列之间的值。使用不同于等于 (=) 的操作符获得关系。

示例一：

创建 job_grades 表，包含 lowest_sal ，highest_sal ，grade_level。

create table job_grades(
lowest_sal int,
highest_sal int,
grade_level varchar(30));

示例二：

插入数据

1000 2999 A
2000 4999 B
5000 7999 C
8000 12000 D

insert into job_grades values(1000,2999,'A');
insert into job_grades values(2000,4999,'B');
insert into job_grades values(5000,7999,'C');
insert into job_grades values(8000,12000,'D');

示例三：

查询所有雇员的薪水级别。

select e.last_name,j.grade_level 
from employees e ,job_grades j 
where e.salary
between j.lowest_sal and j.highest_sal;

自连接

连接一个表到它自己。有时需要连接一个表到它自己。为了找到每个雇员的经理的名字，则需要连接EMPLOYEES 表到它自己，或执行一个自连接。

示例一：

查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为W，经理列别名为M。

select
worker.LAST_NAME W,manager.LAST_NAME M
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID;

示例二：

查询Fox的经理是谁？显示他的名字。

select
worker.LAST_NAME,manager.LAST_NAME
from employees worker,employees manager
where worker.MANAGER_ID = manager.EMPLOYEE_ID
AND worker.LAST_NAME = 'Fox';

SQL99标准中的查询

MySQL5.7 支持部分的SQL99 标准。

SQL99中的交叉连接(CROSS JOIN)

• CROSS JOIN 子句导致两个表的交叉乘积
• 该连接和两个表之间的笛卡尔乘积是一样的

示例：

使用交叉连接查询 employees 表与 departments 表。

select * 
from employees 
cross join departments;

SQL99中的自然连接(NATURAL JOIN)

• NATURAL JOIN 子句基于两个表之间有相同名字的所有列
• 它从两个表中选择在所有的匹配列中有相等值的行
• 如果有相同名字的列的数据类型不同，返回一个错误

自然连接

连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。

自然连接查询

示例一：

select 
DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID,CITY
from departments
NATURAL JOIN locations;

例子中，LOCATIONS 表被用 LOCATION_ID 列连接到DEPARTMENT表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们，连接条件的关系为and。

自然连接也可以被写为等值连接：

SELECT

d.department_id, d.department_name, d.location_id , l.city
FROM departments d , locations l
WHERE d.location_id = l.location_id;

示例二：

使用自然连接查询所有有部门的雇员的名字以及部门名称。

select e.last_name,d.department_name 
from employees e 
natural join departments d;

SQL99中的内连接(INNER JOIN)

语法：

• SELECT 查询列表;
• FROM 表1 别名;
• INNER JOIN 连接表(INNER关键字可省略);
• ON 连接条件;

用ON子句指定连接条件

示例：

查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

select
e.employee_id,e.salary,d.department_name 
from employees e 
inner JOIN departments d on
e.department_id = d.department_id 
where e.last_name = 'Fox';

外连接查询(OUTER JOIN)

• 在SQL: 1999中，连接两个表，仅返回匹配的行的连接，称为内连接
• 在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左(或右)表的连接，称为左(或右)外连接
• 在两个表之间的连接，返回内连接的结果，同时还返回左和右连接,称为全外连接

孤儿数据(Orphan Data)

孤儿数据是指被连接的列的值为空的数据。

左外连接(LEFT OUTER JOIN)

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询也会取回 EMPLOYEES 表中所有的行。

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

select e.last_name,d.department_name 
from employees e 
LEFT OUTER JOIN departments d on 
e.DEPARTMENT_ID = d.department_id;

右外连接(RIGTH OUTER JOIN)

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询也会取回 DEPARTMENTS 表中所有的行。

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

select e.last_name,d.department_name  
from employees e 
RIGHT  OUTER JOIN departments d on 
e.DEPARTMENT_ID = d.department_id;

全外连接(FULL OUTER JOIN)

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

• UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了DISTINCT。
• UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

语法结构

(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名
ON 连接条件)
UNION
(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表
名 ON 连接条件)

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇 员。

(select e.last_name,d.department_name 
from employees e LEFT OUTER JOIN departments d  
on e.department_id = d.department_id)  
UNION  
(select e1.last_name,d1.department_name
from employees e1 RIGHT OUTER JOIN departments d1 
on d1.department_id = e1.department_id);