临川
文章
33
分类
6
评论
0

多表查询

| MySQL •
字数总计 12234 | 阅读时长 30分钟 | 阅读量 151

多表查询：多个表(1个表以上)一起进行查询操作。

前提条件：这些表是有关联字段。

1. 笛卡尔积

当一条 select 语句中，同时查询多个表(1个表以上)，（未指明连接条件时）会将查询的字段值进行全排列。

举例：

SELECT employee_id,department_id
FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

  • 避免笛卡尔积，在 WHERE 加入有效连接条件

  • 加入连接条件后：

SELECT employee_id,department_id
FROM employees,departments
#连接条件
WHERE employees.`department_id` = departments.`department_id`;

#若查询中出现多个表都存在的字段，SELECT 后必须指明此字段所属的表
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id
建议：从 sql 优化的角度，建议多表查询时，每个字段前都指明其所在的表

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;

2. 多表查询的分类

2.1 等值连接与非等值连接

2.1.1 等值连接

简单来说：等值连接就是两表的连接条件字段值是相等的

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;

阿里开发规范：

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

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

连接 n 个表，至少需要 n-1 个连接条件

2.1.2 非等值连接

非等值连接指的是：连接条件字段值是不相等的，可能是某个区间

举例：

SELECT e.last_name,e.salary,j.grader_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`Highest_sal`

2.2 自连接与非自连接

自连接：多表连接查询时，连接的是同一张表

举例：

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;

2.3 内连接与外连接

Snipaste_2022-06-03_17-53-39

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

  • 外连接：结果集中出来包含满足条件的行，还返回左（或右）表中不满足条件的行，这种连接称为：左（或右）外连接。没有匹配的行时，结果集中对应的列为NULL。

    • 若是左外连接，则连接条件中左边的表称为主表，右边的表称为从表
    • 若是右外连接，则连接条件中右边的表称为主表，左边的表称为从表

2.3.1 内连接

Snipaste_2022-06-03_17-53-39
合并有同一列的两个以上的表的行，结果集中不包含表与另外一个表不匹配的行

SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;
有的员工没有部门，有的部门中没有员工

2.3.2 外连接

  • 结果集中出来包含满足条件的行，还返回左（或右）表中不满足条件的行，这种连接称为：左（或右）外连接。没有匹配的行时，结果集中对应的列为NULL。

    • 若是左外连接，则连接条件中左边的表称为主表，右边的表称为从表

    • 若是右外连接，则连接条件中右边的表称为主表，左边的表称为从表

1. SQL92 语法实现多表连接
  • 在 SQL 92 中使用 (+) 在连接条件中代表从表。
  • MySQL 不支持 SQL92 的外连接，Oracle 支持
  • 在 SQL92 中只有左外和右外连接，没有满(或全)外连接
内连接
查询 所有的 即为外连接
查询所有的员工的last_name,department_name 信息
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`; #需要使用左外连接

左外连接：Mysql不支持
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);

右外连接：Mysql不支持
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

3. SQL99 语法实现多表查询

Snipaste_2022-06-03_17-53-39

3.1 基本语法

SELECT 字段列表
FROM A表
JOIN B表 ON A表 与 B表 的连接条件
WHERE 等其他子句;

3.2 内连接(INNER JOIN) 的实现

Snipaste_2022-06-03_17-53-39

  • 语法：
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;
  • 举例1：
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
  • 举例2：
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

3.3 外连接(OUTER JOIN) 的实现

Snipaste_2022-06-03_17-53-39

3.3.1 左外连接(LEFT OUTER JOIN)

  • 语法：
#实现查询结果是 A表不满足条件的行 + 满足条件的行
SELECT 字段列表
FROM A表    LEFT JOIN B表 
ON 关联条件
WHERE 等其他子句;
  • 举例：
SELECT e.last_name, e.department_id, d.department_name 
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

3.3.2 右外连接(RIGHT OUTER JOIN)

#实现查询结果是 B表不满足条件的行 + 满足条件的行
SELECT 字段列表
FROM A表    RIGHT JOIN B表 
ON 关联条件
WHERE 等其他子句;

举例：

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

3.3.3 满外连接(FULL OUTER JOIN)

  • 满外连接结果集 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。

  • 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

4. UNION 的使用

UNION：合并查询结果

SELECT column,...FROM table1
UNION [ALL]
SELECT column,...FROM table2

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

Snipaste_2022-06-04_11-38-24

UNION ALL：返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

Snipaste_2022-06-04_11-39-39

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

5. 7种SQL JOIN 的实现

Snipaste_2022-06-04_11-45-32

中图:内连接 A ∩ B
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e (INNER) JOIN departments d
ON e.`department_id` = d.`department_id`;

左上图：左外连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e LEFT (INNER) JOIN departments d
ON e.`department_id` = d.`department_id`;

右上图：右外连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e RIGHT (INNER) JOIN departments d
ON e.`department_id` = d.`department_id`;

左中图：A - A∩B
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e LEFT (INNER) JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.department_id IS NULL;

右中图：B - A∩B
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e RIGHT (INNER) JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.department_id IS NULL;

左下图：满外连接左下图：左上图 + 右中图
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
LEFT (INNER) 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 (INNER) JOIN departments d ON e.`department_id` = d.`department_id`
WHERE e.department_id IS NULL;

小结：

  • 左中图
#实现A -  A∩B
select 字段列表
from A表    left join B表 
on 关联条件
where 从表关联字段 is null and 等其他子句;
  • 右中图
#实现B -  A∩B
select 字段列表
from A表    right join B表 
on 关联条件
where 从表关联字段 is null and 等其他子句;
  • 左下图
#实现查询结果是A∪B
#用左外的A，union 右外的B 
select 字段列表
from A表    left join B表 
on 关联条件
where 等其他子句
union
select 字段列表
from A表    right join B表 
on 关联条件
where 等其他子句;
  • 右下图
#实现A∪B - A ∩ B 或(A - A∩B)∪(B - A∩B)
#使用左外的    (A -  A∩B)  union 右外的（B - A∩B） 
select 字段列表
from A表    left join B表 
on 关联条件
where 从表关联字段    is null and 等其他子句 
union
select 字段列表
from A表    right join B表 
on 关联条件
where 从表关联字段    is null and 等其他子句

6. SQL99新特性

6.1 自然连接

自然连接可以理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

在 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`;

在 SQL 99 中：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

6.2 USING 连接

USING 指定两表同名字段进行等值连接

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

等同于

SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
文章作者： 临川
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 临川羡鱼！
MySQL MySQL
喜欢就支持一下吧