1.17 多表查询 🎉

刘春龙...大约 6 分钟

1.17 多表查询 🎉

笛卡尔乘积

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

任何一个多表查询的结果集都是笛卡尔乘积的子集

多表查询分类

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

sql92标准 💎

等值连接 👻

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

等值连接特点：

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

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

等值连接的使用

SELECT 子句指定要返回的列名：
- employee last name、employee number 和 department number，这些是EMPLOYEES 表中的列
- department number、department name 和 location ID，这些是 DEPARTMENTS 表中的列
FROM 子句指定数据库必须访问的两个表：
- EMPLOYEES 表
- DEPARTMENTS 表
WHERE 子句指定表怎样被连接：
- EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列是两个表的同名列，它必须用表名做前缀以避免混淆。

增加搜索条件

添加查询条件

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

限制不明确的列名

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

使用表别名

表别名定义原则

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

多表连接

示例一：查询雇员 King 所在的部门名称。

select d.department_name from employees e,departments d  where e.dept_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 表到它自己，或执行一个自连接。

图片中的例子连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表，对于相同的表 EMPLOYEES，用两个别名，分别为 worker 和 manager。在该例中，WHERE 子句包含的连接意味着 “一个工人的经理号匹配该经理的雇员号”。

示例一：查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为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标准 💎

交叉连接 👻

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

select * from employees cross join departments;

自然连接 👻

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

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

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

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

提示

自然连接也可以用sql92标准的等值连接来表示

内连接 👻

语法：

SELECT 查询列表;
FROM 表1 别名;
INNER JOIN 连接表(INNER关键字可省略);
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';

左外连接与右外连接 👻

左外连接

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

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

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

右外连接

右边的表 (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;

全外连接 👻

注意

注意： 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)

昵称

邮箱

网址

按正序
按倒序
按热度

1.17 多表查询 🎉

# 1.17 多表查询 🎉

# sql92标准 💎

# 等值连接 👻

# 等值连接的使用

# 增加搜索条件

# 添加查询条件

# 限制不明确的列名

# 使用表别名

# 多表连接

# 非等值连接 👻

# 自连接 👻

# sql99标准 💎

# 交叉连接 👻

# 自然连接 👻

# 内连接 👻

# 左外连接与右外连接 👻

# 左外连接

# 右外连接

# 全外连接 👻

预览:

1.17 多表查询 🎉

sql92标准 💎

等值连接 👻

等值连接的使用

增加搜索条件

添加查询条件

限制不明确的列名

使用表别名

多表连接

非等值连接 👻

自连接 👻

sql99标准 💎

交叉连接 👻

自然连接 👻

内连接 👻

左外连接与右外连接 👻

左外连接

右外连接

全外连接 👻