（八）多表查询

article/2025/9/24 6:58:04

目录

多表查询

笛卡尔积的错误

连接条件

表的别名

多表查询分类

自连接  和  非自连接 

内连接  和  外连接

SQL92

SQL99

UNION的使用

UNION操作符

UNION ALL操作符

7种SQL JOINS的实现

QL99语法新特性

自然连接

USING连接

多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个 关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进 行关联。

目前，如果要多表查询，就只能一张一张表去找，没有形成关联，速度与效率较慢，代码如下所示：

SELECT * 
FROM employees
WHERE last_name = 'Abel';    # 找到所在部门SELECT *
FROM departments
WHERE department_id = 80;    # 找所在位置SELECT *
FROM locations 
WHERE location_id = 2500;    # 找居住城市

笛卡尔积的错误

#错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

错误的原因：

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

笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交 叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

#错误的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出2889条记录

连接条件

为了避免笛卡尔积， 应该在 WHERE 加入有效的连接条件，具体代码如下所示。

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.`department_id`;

 注：在表中有相同列时，需要在列名之前加上表名前缀，如下所示。

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

如果有n个表实现多表的查询，则需要至少n-1个连接条件，具体代码如下所示：

#练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

表的别名

  • 使用别名可以简化查询。
  • 列名前使用表名前缀可以提高查询效率。

因为有些表名比较的长，所以可以考虑起别名的方法，但是要注意，根据代码的执行次序，表起了别名之后，只能使用别名来操作，具体代码如下所示：

#给表起别名，在SELECT和WHERE中使用表的别名。
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;
#如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
#如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

多表查询分类

等值连接 和 非等值连接

我们之前用等号连接的，都是等值连接，接下来主要介绍非等值连接，也就是在一个范围内相等的条件，代码如下所示：

SELECT e.last_name,e.salary,j.grade_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`;

运行结果如下所示：

自连接  和  非自连接 

我们之前都是不同的表相连接，现在主要介绍自己与自己相连接的情况，主要解决办法就是把一个表看成两个表，然后起不同的别名，再连接，具体代码如下所示：

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

运行结果如下所示：

内连接  和  外连接

  • 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
  • 外连接: 两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
  • 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
  • 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。
  • 满外连接，同时满足左外连接和右外连接。

 因为SQL92语法，与SQL99语法对于实现内连接  和  外连接有所不同，于是分别讨论两种语法的实现，如下所示。

SQL92

SQL92语法实现内连接：和我们之前的实现是一样的，就不再重复。

SQL92语法实现外连接：使用 +  ----------MySQL不支持SQL92语法中外连接的写法

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);
# 用哪一部分缺少数据，就在那一部分加上一个加号，上面实现的是左外连接

SQL99

SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的。

SQL99语法实现内连接：

SELECT last_name,department_name,city
FROM employees e INNER 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 JOIN departments d
ON e.`department_id` = d.`department_id`;

运行结果如下所示：

 右外连接：

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

运行结果如下所示：

 满外连接：mysql不支持FULL OUTER JOIN，具体解决方案，在后面给出。

UNION的使用

合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

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

UNION操作符

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

UNION ALL操作符

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

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

7种SQL JOINS的实现

 中图：内连接

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

 左上图：左外连接

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

右上图：右外连接

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

左中图：

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;

运行结果如下所示：

 右中图：

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

运行结果如下所示：

 左下图：满外连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# 使用UNION ALL语句连接两个表
# 方式1：左上图 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`;
# 方式2：左中图 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`
WHERE e.`department_id` IS NULL;
# 右下图：左中图  UNION ALL 右中图

运行结果如下所示：

QL99语法新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 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`;
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

运行结果两者是相同的，还是建议前者，后者有局限性。

USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如：

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

这两种是相等的，但是有局限性，不能自连接，不太建议使用。


http://chatgpt.dhexx.cn/article/pX3CJ145.shtml

相关文章

6、多表查询

6、多表查询

第06章_多表查询 多表查询&#xff0c;也称为关联查询&#xff0c;指两个或更多个表一起完成查询操作。 前提条件&#xff1a;这些一起查询的表之间是有关系的&#xff08;一对一、一对多&#xff09;&#xff0c;它们之间一定是有关联字段&#xff0c;这个关联字段可能建立了…
阅读更多...
【MySQL】多表查询方法

【MySQL】多表查询方法

目录 多表关系 多表查询之笛卡尔积 多表查询分类 连接查询 内连接 外连接 自连接 子查询 标量子查询 列子查询 行子查询 表子查询 联合查询 多表关系 一对多&#xff08;多对一&#xff09;&#xff1a;员工与部门&#xff0c;一个部门可以对应多个员工一般在多的…
阅读更多...
【MySQL】多表查询

【MySQL】多表查询

首先&#xff0c;为了方便说明问题&#xff0c;创建两个表emp&#xff08;雇员信息&#xff09;和dept&#xff08;雇员部门信息&#xff09;&#xff0c;其数据如下&#xff1a; 在之前的博客中&#xff0c;我们分享了单表查询的方法&#xff0c;但是在实际应用中&#xff…
阅读更多...
mysql 多表查询

mysql 多表查询

一、概念 多表查询也叫关联查询, 其就是指 一次性查询多张表&#xff0c;并且将每张表得到的数据联系作为查询结果 二、关键字 当我们需要一次查询两张表时&#xff0c;如果使用&#xff1a; select * from employees,departmaents; 这样的话会出现笛卡尔积错误 (笛卡尔积错…
阅读更多...
数据库-MySQL-多表查询（总结）

数据库-MySQL-多表查询（总结）

目录 前言 多表查询概述 多表查询分类 1、连接查询 2、子查询 内连接 内连接查询语法 案例演示 外连接 外连接查询的语法 案例演示 联合查询-union,union all 子查询 概念 分类 标量子查询 自连接 自连接查询语法 案例演示 子查询 列子查询 行子查询 表子…
阅读更多...
多表查询（整理）

多表查询（整理）

一、多表关系 概述&#xff1a;项目开发中&#xff0c;在进行数据库表结构设计时&#xff0c;会根据业务需求及业务模块之间的关系&#xff0c;分析并设计表结构&#xff0c;由于业务之间互相关联&#xff0c;所以各个表结构之间也存在着各种联系。 基本上有三种&#xff1a;…
阅读更多...
mybatis多表查询

mybatis多表查询

目录 前言 一、Mybatis多表查询 2.一对一查询&#xff08;多对一&#xff09; 2.2 一对多查询 2.3 多对多查询 2.4 知识小结 前言 mybatis初学者记录每日学习日常 提示&#xff1a;以下是本篇文章正文内容&#xff0c;下面案例可供参考 一、Mybatis多表查询 内连接、左外连…
阅读更多...
多表查询（连接查询）

多表查询（连接查询）

在使用数据库查询语句时&#xff0c;单表的查询有时候不能满足项目的业务需求&#xff0c;在项目开发过程中&#xff0c;有很多需求都是要涉及到多表的连接查询最终需要查询的数据来源于不同的表中时&#xff0c;这个时候需要考虑使用连接查询 新建一张员工表 -- emp drop t…
阅读更多...
数据库高级——多表查询

数据库高级——多表查询

多表查询 多表查询有如下几种&#xff1a; 合并结果集&#xff1a;union、union all连接查询&#xff1a; 2.1. 内连接&#xff1a;inner join on 2.2. 外连接&#xff1a;outer join on – 左外连接&#xff1a;left outer join – 右外连接&#xff1a;right outer join 2.…
阅读更多...
MySQL多表查询

MySQL多表查询

文章目录 一、什么是多表查询1、概述2、笛卡尔积现象 二、多表查询的分类1、等值连接 vs非等值连接2、自连接 vs 非自连接3、内连接 vs 外连接4、满外连接&#xff08;FULL OUTER JOIN&#xff09; 三、UNION[ALL]的使用四、7种SQL JOINS的实现 一、什么是多表查询 1、概述 多…
阅读更多...
【MySQL】多表查询策略（多表联查子查询）

【MySQL】多表查询策略（多表联查子查询）

目录 一、MySQL多表查询 二、准备工作 1.运行环境 2.创建公司表 3.创建员工表 三、多表查询 &#xff08;一&#xff09;多表联查—同时查询多张表 1.联结 2.左连接 3.右连接 4.内连接 &#xff08;二&#xff09;子查询 1.SELECT子查询 2.WHERE子查询 3.FROM子…
阅读更多...
MySQL----多表查询

MySQL----多表查询

MySQL----多表查询 多表关系表与表之间的联系&#xff1a;一对多(多对一)多对多一对一 多表查询多表查询的分类数据准备内连接外连接自连接联合查询子查询标量子查询列子查询行子查询表子查询 多表查询案例 多表关系 在进行数据库表结构的设计时&#xff0c;会根据业务的需求和…
阅读更多...
SQL语句多表查询

SQL语句多表查询

多表查询 多表查询&#xff0c;也称为关联查询&#xff0c;指两个或更多个表一起完成查询操作。 前提条件&#xff1a;这些一起查询的表之间是有关系的&#xff08;一对一、一对多&#xff09;&#xff0c;它们之间一定是有关联字段&#xff0c;这个关联字段可能建立了外键&a…
阅读更多...
十、多表查询

十、多表查询

十、多表查询 *多表查询的本质就是把多张表合并成一个临时表 1、多表数据合并方案 连接查询合并方案【最常见】 首先确保两张表之间存在【隶属关系】将两张表中的数据行【沿着水平方向进行拼接】最终得到一个拥有【完整隶属关系】的新数据行 联合查询合并方案 不需要两张表之…
阅读更多...
图解数据库多表查询（多表联查）的几种方式

图解数据库多表查询（多表联查）的几种方式

多表查询&#xff0c;也被称为多表连接查询。 为什么会有多表查询这个操作呢&#xff1f; 在实际项目开发过程中&#xff0c;会出现多张表&#xff0c;用于分类存储不同的信息&#xff0c;我们测试需要的数据也会来自不同的表&#xff0c;因此需要使用多表查询。 我们常用的…
阅读更多...
史上最牛mysql-06 (多表连接)

史上最牛mysql-06 (多表连接)

多表连接 个人博客 &#xff1a;www.xiaobeigua.icu 多表查询&#xff0c;也称为关联查询&#xff0c;指两个或更多个表一起完成查询操作。 前提条件&#xff1a;这些一起查询的表之间是有关系的&#xff08;一对一、一对多&#xff09;&#xff0c;它们之间一定是有关联字段&…
阅读更多...
sql多表查询

sql多表查询

1.多表查询概念&#xff1a;从多张表查询数据 2.分类&#xff1a;连接查询和子查询。 有两张表A、B 连接查询&#xff1a; 内连接 相当于查询A、B交集数据 例1&#xff1a;select * from emp,dept where emp.dep_iddept.id; 外连接 左外连接&#xff1a;相当于查询…
阅读更多...
sql语句之多表查询

sql语句之多表查询

文章目录 一、笛卡尔积(交叉连接) ---->cross join1、概念2. 语法3.示例 二、内连接---->inner join1、概念2. 语法3.示例 三、外链接---->outer join1、概念2. 语法3.示例 四、联合连接---->union1、概念2.示例 一、笛卡尔积(交叉连接) ---->cross join 1、概…
阅读更多...
连接查询（多表查询）——MySQL

连接查询（多表查询）——MySQL

连接查询&#xff08;多表查询&#xff09; 又称多表查询&#xff0c;当查询的字段涉及多个表的时候&#xff0c;就要用到连接查询 分类&#xff1a; 为表起别名&#xff1a; 提高语句的简洁度区分多个重名字段注意&#xff1a;如果为表起了别名&#xff0c;则查询的字段就不…
阅读更多...
MySQL-多表查询

MySQL-多表查询

一、表架构 student(sid,sname,sage,ssex) 学生表course(cid,cname,tid) 课程表sc(sid,cid,score) 成绩表teacher(tid,tname) 教师表 二 、建表sql语句 SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0;-- ---------------------------- -- Table structure for course -- --…
阅读更多...
推荐文章

Copyright @ 2022~2023