SQL数据查询——连接查询

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: SQL数据查询——连接查询

什么是连接查询?

连接查询也称多表查询,是涉及两个以上的表的查询,也是数据库查询中最普遍的一种查询。

其中包括等值连接查询、自然连接查询、非等值连接查询、自身连接(自连接)查询、外连接(左外连接、右外连接)查询和复合条件查询等。

查询格式

  • 用SQL语句进行连接查询时,在FROM子句中选择需要连接的多个表。
  • 那么如何选择在连接的属性呢?WHERE子句中用来连接两个表的条件称为连接条件连接谓词
  • 除此之外,还可以用连接查询特定的语句,即JOIN ON子句。只需要在JOIN后面添加要连接的表,在ON后面添加连接条件,在特定的情况下变换格式就可以实现所有的连接查询。

代码格式

#使用FROM WHERE子句时的格式:
SELECT <属性名>  #可选择多个
FROM <表名>   #选择多个
WHERE [<表名1>.]<属性名1> <比较运算符> [<表名2>.]<属性名2> #连接条件
#使用JOIN ON子句时的格式
SELECT <属性名>        #可选择多个
FROM <表名1> JOIN <表名2> #选择要连接的表(两个)
ON [<表名1>.]<属性名1> <比较运算符> [<表名2>.]<属性名2>  #连接条件
JOIN ...... ON ......   #可继续连接多个表
WHERE [<表名1>.]<属性名1> <运算符> <限定条件>     #其他限定条件

一、等值和非等值连接查询

1.等值连接查询

什么是等值连接查询

  • 例如下面的三张表Student表存储的是学生的基本信息,Course表存储的是每一门课的课号以及先行课的课号,SC表存储的是学生的选课信息和考试成绩。

  • 学生表和课程表有相同的字段,但是如果要寻找某个学生的姓名对应的成绩,则需要先找出姓名对应的学号,再到课程表找到成绩
  • 等值查询就是解决这种问题,可以把两个表使用相同的字段连接称为一个大表,然后利用投影选择需要的属性列。可以理解为在等值连接下的查询

示例

#查询每个学生及其选修课程的情况(方式一)
SELECT *
FROM student, sc
WHERE student.`Sno`=sc.`Sno`;
#查询每个学生及其选修课程的情况(方式二)
SELECT student.`Sno`, student.`Sname`,sc.`Cno`
FROM student, sc
WHERE student.`Sno`=sc.`Sno`;
#使用JOIN ON格式
SELECT student.`Sno`, student.`Sname`,sc.`Cno`
FROM student JOIN sc
ON student.`Sno`=sc.`Sno`;

方式一:

方式二:

2.非等值连接查询

什么是非等值连接查询

  • 在某些查询情况中,可能不会查询某个值在另一个表中对应的准确信息
  • 而需要查询表中的值在另一个表中对应的范围
  • 这种连接条件运算符是=之外的连接查询称为非等值查询

示例

#查询有工资员工的工资等级
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`;


二、自连接与非自连接

上面涉及到的所有操作都是非自连接,那么什么是自连接呢?

  • 连接操作不仅可以在两个表之间进行,还可以是一个表与其自己进行连接,称为表的自身连接(自连接)
  • 例如下面的员工表,如果要查询员工id,员工姓名及其管理者的id和姓名
  • 需要先找到每个员工的管理者的id,再根据管理者的id到这张表中找到对应的名字

示例

#自连接通过给一个表起两个别名来实现
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`; #连接条件为这个表的不同字段的比较


三、内连接与外连接

1.内连接

什么是内连接

合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

  • 上面的操作都是内连接
  • 比如上面的Course表和SC表中都有Cno字段,但是并不是所有的课程都被学生选择了
  • 如果将这两个表通过课号Cno进行连接,只会出现在两个表中都出现的课号
  • 这种只出现两个连接字段公共元组的情况为内连接

示例

#查询学生的选课情况
SELECT *
FROM sc JOIN course   #课程表和选课表以课号连接
ON sc.`Cno`=course.`Cno`

2.外连接

什么是外连接

关系代数这一篇文章中介绍了定义,在这里再举例说明一下

  • 对于上面内连接示例介绍的情况,如果要把所有的课程都加进去
  • 则需要用外连接,即把悬浮元组也保存到结果关系中,在其他属性上填空值(NULL)的连接就是外连接

示例

#查询学生的选课情况
SELECT *
FROM sc RIGHT JOIN course #这里使用右外连接
ON sc.`Cno`=course.`Cno`

使用左外连接还是右外连接?

左外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行,这种连接称为左外连接。

右外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行,这种连接称为右外连接。

  • 这个问题没有固定的答案,需要看连接表的位置
  • 一般来说,当左表数据要超出右表数据时,使用左外连接
  • 当左表数据要小于右表数据时,使用右外连接

满外连接

当我们需要的数据在左右表都有没匹配的情况的时候,需要用到满外连接

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

  • SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
  • 需要注意的是,MySQL不支持FULL JOIN,但是可以用 LEFT JOIN UNION RIGHT JOIN代替。
#满外连接:mysql不支持FULL OUTER JOIN (Oracle)
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

四、UNION 的使用

什么时候使用UNION关键字?

当我们有多条SELECT查询语句,并且需要将这几个查询结果合并成为一个大表时(合并查询结果),我们需要用到UNION

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

使用 UNION 还是 UNION ALL ?

差别:

  • UNION:会执行去重操作
  • UNION ALL:不会执行去重操作

结论:

  • 如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率

五、各种形式集合关系的SQL实现

对于中间的图,很显然是内连接,不涉及不匹配的数据。

对于左上图,是左外连接,右上图是右外连接,在上面已经介绍了。

对于左中图,是左外连接去掉中间的内连接部分的情况,所以如果在实例中,需要明确中间部分是什么数据,进而知道用什么样的条件可以限制这些数据。右中图同样的道理。

对于左下图是满外连接,根据上一节的介绍,可以使用 UNION ALL 将左上图与右中图,或者由上图与左中图进行合并。要尽可能避免去重,所以要尽量选择没有公共部分的集合。

当然,如果使用Oracle,也可以使用 FULL OUTER JOIN

对于右下图,在左下图的基础上去掉公共部分,此操作与中图相同。


六、语法差异

  1. SQL99中,NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ,然后进行等值连接 。但是不够灵活,自然连接是所有相同的字段都进行等值连接
    如果是在SQL92中,则需要手动输入连接条件,并且去除重复字段
  2. 在Oracle中,可以使用+来实现外连接,而MySQL不可以
  3. 在Oracle中使用FULL OUTER JOIN实现满外连接,而MySQL中使用UNION来实现
  4. 在SQL99中,使用USING关键字可以将两个表中的同名字段进行连接
# 案例一、SQL99语法的新特性1:自然连接
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`;      #SQL92语法格式
# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`,然后进行`等值连接`。
# 但是不够灵活,自然连接是所有相同的字段都进行等值连接
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;  #SQL99语法格式
# 案例二、Oracle与MySQL外连接语法
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+); #Oracle
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;   #MySQL
# 案例三、Oracle与MySQL满外连接语法
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d  #Oracle
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`
UNION ALL                   #MySQL
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;
# 案例四、SQL99语法的新特性2:USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;     #SQL92语法格式
# USING替换连接条件,当使用两个表中的字段名一样时,可以使用
# 不适用于自连接,因为两个字段名字不一样
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);              #SQL99语法格式

注意事项

  1. 出现笛卡尔积的错误:
    当未注明连接条件时,系统会默认进行笛卡尔积,所以我们要注意连接条件
  2. 如果查询语句中出现了多个表中都存在的字段,则必须指明此字段所在的表。
    从sql优化的角度,建议多表查询时,每个字段前都指明其所在的表。
  3. 可以给表起别名,在SELECT和WHERE中使用表的别名。
    一般来说为了方便都会给表起别名,如果给表起了别名,一旦在SELECT或WHERE中使用表名的话,则必须使用表的别名,而不能再使用表的原名。
  4. 注意查询条件个数
    如果有n个表实现多表的查询,则需要至少n-1个连接条件
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
22天前
|
SQL 监控 关系型数据库
一键开启百倍加速!RDS DuckDB 黑科技让SQL查询速度最高提升200倍
RDS MySQL DuckDB分析实例结合事务处理与实时分析能力,显著提升SQL查询性能,最高可达200倍,兼容MySQL语法,无需额外学习成本。
|
22天前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
21天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
4月前
|
SQL 数据挖掘 数据库
第三篇:高级 SQL 查询与多表操作
本文深入讲解高级SQL查询技巧,涵盖多表JOIN操作、聚合函数、分组查询、子查询及视图索引等内容。适合已掌握基础SQL的学习者,通过实例解析INNER/LEFT/RIGHT/FULL JOIN用法,以及COUNT/SUM/AVG等聚合函数的应用。同时探讨复杂WHERE条件、子查询嵌套,并介绍视图简化查询与索引优化性能的方法。最后提供实践建议与学习资源,助你提升SQL技能以应对实际数据处理需求。
301 1
|
26天前
|
SQL XML Java
通过MyBatis的XML配置实现灵活的动态SQL查询
总结而言,通过MyBatis的XML配置实现灵活的动态SQL查询,可以让开发者以声明式的方式构建SQL语句,既保证了SQL操作的灵活性,又简化了代码的复杂度。这种方式可以显著提高数据库操作的效率和代码的可维护性。
129 18
|
1月前
|
SQL 人工智能 数据库
【三桥君】如何正确使用SQL查询语句:避免常见错误?
三桥君解析了SQL查询中的常见错误和正确用法。AI产品专家三桥君通过三个典型案例:1)属性重复比较错误,应使用IN而非AND;2)WHERE子句中非法使用聚合函数的错误,应改用HAVING;3)正确的分组查询示例。三桥君还介绍了学生、课程和选课三个关系模式,并分析了SQL查询中的属性比较、聚合函数使用和分组查询等关键概念。最后通过实战练习帮助读者巩固知识,强调掌握这些技巧对提升数据库查询效率的重要性。
88 0
|
2月前
|
SQL
SQL中如何删除指定查询出来的数据
SQL中如何删除指定查询出来的数据
|
2月前
|
SQL XML Java
配置Spring框架以连接SQL Server数据库
最后,需要集成Spring配置到应用中,这通常在 `main`方法或者Spring Boot的应用配置类中通过加载XML配置或使用注解来实现。
247 0
|
4月前
|
SQL 关系型数据库 MySQL
凌晨2点报警群炸了:一条sql 执行200秒!搞定之后,我总结了一个慢SQL查询、定位分析解决的完整套路
凌晨2点报警群炸了:一条sql 执行200秒!搞定之后,我总结了一个慢SQL查询、定位分析解决的完整套路
凌晨2点报警群炸了:一条sql 执行200秒!搞定之后,我总结了一个慢SQL查询、定位分析解决的完整套路
|
3月前
|
SQL 存储 弹性计算
OSS Select 加速查询:10GB CSV 文件秒级过滤的 SQL 语法优化技巧
OSS Select 可直接在对象存储上执行 SQL 过滤,跳过文件下载,仅返回所需数据,性能比传统 ECS 方案提升 10~100 倍。通过减少返回列、使用等值查询、避免复杂函数、分区剪枝及压缩优化等技巧,可大幅降低扫描与传输量,显著提升查询效率并降低成本。