开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL 多表查询 -> 正文阅读

[大数据]MySQL 多表查询

📢跟随B站宋红康老师【康师傅】学习MySQL🧑?💻

MySQL 多表查询

多表查询概念

多表查询：也称关联查询，指两个表或更多的表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段的，这个关联字段可能建立了外键，也可能没有建立外键。

多表查询的引出

现有如下三个表：
在这里插入图片描述
通过观察三个表我们发现，其实这三个表可以关联在一起的，EMPLOYEES表和DEPARTMENTS表有共同的字段department_id，
DEPARTMENTS表和LOCATIONS表有共同的字段location_id。

需求： 现已知有一个员工名为Gee，查询其所在的城市。
因为三个表是有关联的，我们可以通过EMPLOYEES表找到Gee的department_id，然后通过DEPARTMENTS表找到Gee的location_id，最后通过LOCATIONS表找到Gee的城市。
在这里插入图片描述
查询如下：
1）通过last_name在employees表中查询到Gee的department_id为50。

SELECT 
  *
FROM
  employees 
WHERE last_name = 'Gee' ;

在这里插入图片描述
2）通过department_id在departments表中查询到Gee的location_id为1500

SELECT 
  * 
FROM
  departments 
WHERE department_id = 50 ;

在这里插入图片描述
3）通过location_id在locations表中查询到Gee的城市为South San Francisco

通过表的关联关系我们分3次在3个不同的表中查询到了Gee的所在城市，在查询过程中我们体会到查询的不方便，如果有更多的表就更麻烦了，而且还有影响客户端和服务器端的查询效率，所以此时多表查询就极为关键了。

笛卡尔积

需求： 我们用多表查询员工的姓名及其部门名称。
代码如下： (错误的多表查询)

SELECT 
  last_name,
  department_name 
FROM
  employees,
  departments ;

在这里插入图片描述
查看查询结果我们发现居然查询到了2889条信息？可是我们的员工表中才有107条信息，也就是说一共才有107个员工，所以查询结果肯定是不正确的。
分析问题：
查看employes表中的行数

SELECT 
  COUNT(employee_id) 
FROM
  employees ;

在这里插入图片描述
查看departments表的行数

SELECT 
  COUNT(department_id) 
FROM
  departments ;

在这里插入图片描述
通过两个表中行数相乘计算 107 × 27 = 2889正好是2889。这就是笛卡尔积现象，employees表中的每一行与departments表中的每一行都结合了。

笛卡尔积的错误产生条件

省略多个表的连接条件（或关联条件）
连接条件（或关联条件）无效
所有表中的所有行互相连接

避免笛卡尔积

因此，我们以上的查询方法是不正确的，为避免产生笛卡尔积想象，在进行多表查询的时候一定要用 WHERE 加入有效的连接条件 。
上述正确的查询语句：

# 查询员工的姓名及其部门名称
SELECT 
  last_name,
  department_name 
FROM
  employees,
  departments 
WHERE employees.`department_id` = departments.`department_id` ;# 连接条件

多表查询

多表查询语法

SELECT table1.column, table2.column ... 
FROM table1, table2
WHERE table1.column1 = table2.column2 ...; #连接条件

多变查询注意事项

如果查询语句中出现多个表中都存在的字段，则必须指明此字段所在表。
建议：从SQL优化的角度，建议多表查询时，每个字段前都指明其所在表。

用别名进行多表查询

需求： 查询员工的employee_id，last_name，department_name，city

SELECT 
  employees.`employee_id`,
  employees.`last_name`,
  departments.`department_name`,
  locations.`city` 
FROM
  employees,
  departments,
  locations 
WHERE employees.`department_id` = departments.`department_id` 
  AND departments.`location_id` = locations.`location_id` ;

观察上面的查询语句，我们发现代码显得格外的繁琐，我们可以通过给表起别名的方式来使得查询语句更加直观。

SELECT 
  e.`employee_id`,
  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` ;

通过别名多表查询注意事项

一旦给表起了别名，如果在SELECT或WHERE中使用表名的话，就必须使用别名，而不能再使用表的原名，否则就会报错。

多表查询分类

角度1：等值连接、非等值连接
角度2：自连接、非自连接
角度3：内连接、外连接

等值连接和非等值连接

等值连接

等值连接：就是连接条件是用等号把两个表连接起来的多表查询，上述示例中都为等值连接。

非等值连接

非等值连接：就是连接条件不是用等号把两个表连接起来的多表查询，不局限于 != 也可以为 >、 < 、>=、 <=等等

示例： 查询每个员工的工资等级
首先查看job_grades表

SELECT * FROM job_grades ;

在这里插入图片描述
观察查询结果每个工资等级都有自己的工资范围。

查询语句如下：

# 查询每个员工的工资等级
SELECT 
  e.last_name,
  e.salary,
  j.grade_level 
FROM
  employees e,
  job_grades j 
WHERE e.`salary` >= j.`lowest_sal` 
  AND e.`salary` <= j.`highest_sal` ;

自连接和非自连接

自连接

自连接：就是在同一个表上进行多表查询，这句话可能会觉得矛盾，怎么会在一个表上进行多表查询呢？如下图中一个人的员工编号可以作为另一个员工的主管编号，符合如下条件的表就可以进行自连接。
在这里插入图片描述
需求： 查询员工id，员工姓名及其管理者的id和姓名
分析： 我们可以把员工表看成两个表如下，

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

非自连接

非自连接：就是在多个表中进行的多表查询。

内连接和外连接

内连接：合并具有同一列的两个表以上的行，结果集中不包含一个表与另一表不匹配的行。
在这里插入图片描述
外连接：分为左外连接和右外连接和满外连接
左外连接两个表在连接过程中除了返回满足条件的行以外，还返回左表中不满足条件的行，这种连接称为左外连接。没有匹配的行时，结果表中相应的列为null。
在这里插入图片描述

右外连接两个表在连接过程中除了返回满足条件的行以外，还返回右表中不满足条件的行，这种连接称为右外连接。没有匹配的行时，结果表中相应的列为null。
在这里插入图片描述

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

主表和从表：如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表 。如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表 。

举个栗子🌰
在上述查询中，都为内连接，比如需求： 查询员工的姓名及其部门名称

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

在这里插入图片描述
这就是一个内连接，我们发现查询结果中有106行，可以我们的员工表中一共有107个员工，这就说明，有一个员工没有部门，如果这时也要在查询结果中把这个人展示出来，就是一个左外连接。同时，在部门表中也应该有的部门没有员工，这时在查询结果中把没有员工的部门同时展示出来，就是一个外连接。

SQL92语法

内连接

SQL92语法中内连接 where 后跟连接条件。以上都为SQL92语法。

外连接

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。很可惜，在MySQL中不支持SQL92标准中的左外连接和右外连接。

SQL99语法

SQL99语法使用JOIN…ON子句创建连接的语法结构

SELECT table1.column, table2.column,table3.column
FROM table1
	JOIN table2 ON table1 和 table2 的连接条件
       JOIN table3 ON table2 和 table3 的连接条件

SQL99采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使有再多的表进行连接也都清晰可见。

语法说明

可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其他条件分开的。
ON 子句使语句具有更高的可读性。
关键字JOIN、INNER JOIN、CROSS JOIN的含义一样，都表示内连接。

内连接

语法：

SELECT 字段列表
FROM A表 INNER JOIN B表 # INNER可省略
ON 关联条件
WHERE 等其他子句;

示例一： 查询员工的姓名和部门名

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

示例一： 查询员工的姓名和部门名以及所在城市

SELECT 
  last_name,
  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` ;

左外连接

语法：

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

案例： 查询所有员工的姓名和部门名

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

查询结果： 共107行查询结果，并且一名员工的部门为null
在这里插入图片描述

右外连接

语法：

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

案例: 查询所有部门的员工姓名

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

查询结果： 共122条查询结果，相比于内连接多了16条部门没有员工的查询结果
在这里插入图片描述

UNION

合并查询的结果可以用UNION关键字，可以给出多条SELECT语句，并将他们的结果组合成单个结果集。
使用UNION条件两个表对应的列数和数据类型必须相同，并且互相对应。

语法格式

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

UNION操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录
在这里插入图片描述

UNION ALL操作符

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
在这里插入图片描述
【推荐使用UNION ALL】 不用去重，查询效率高！

7中SQL JOIN的实现

在这里插入图片描述 示例：
中间图内连接 A∩B

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

左上图左外连接

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

右上图右外连接

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

左中图 A - A∩B

SELECT 
  last_name,
  department_name 
FROM
  employees e 
  LEFT JOIN departments d 
    ON e.`department_id` = d.`department_id` 
WHERE d.department_name IS NULL;

右中图 B-A∩B

SELECT 
  last_name,
  department_name 
FROM
  employees e 
  RIGHT JOIN departments d 
    ON e.`department_id` = d.`department_id` 
WHERE e.`last_name` IS NULL;

左下图可以看做左上图 + 右中图

SELECT 
  last_name,
  department_name 
FROM
  employees e 
  LEFT JOIN departments d 
    ON e.`department_id` = d.`department_id` 
UNION ALL 
SELECT 
  last_name,
  department_name 
FROM
  employees e 
  RIGHT JOIN departments d 
    ON e.`department_id` = d.`department_id` 
WHERE e.`last_name` IS NULL ;

右下图可以看做左中图 + 右中图

SELECT 
  last_name,
  department_name 
FROM
  employees e 
  LEFT JOIN departments d 
    ON e.`department_id` = d.`department_id` 
WHERE d.department_name IS NULL
UNION ALL
SELECT 
  last_name,
  department_name 
FROM
  employees e 
  RIGHT JOIN departments d 
    ON e.`department_id` = d.`department_id` 
WHERE e.`last_name` IS NULL;