当前位置: 开发笔记 > 数据库 > 正文

SQLJoin的一些总结(实例)

作者：涵涵幸福2010_759 | 来源：互联网 | 2022-01-22 07:56

Join是关系型数据库系统的重要操作之一，SQLServer中包含的常用Join：内联接、外联接和交叉联接等

1.1.1 摘要
Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据，这时我们应该考虑使用Join，因为Join具体联接表或函数进行查询的特性

本文将通过具体例子介绍SQL中的各种常用Join的特性和使用场合：

1.1.2 正文
首先我们在tempdb中分别定义三个表College、Student和Apply，具体SQL代码如下：

代码如下:

 
USE tempdb 

---- If database exists the same name datatable deletes it. 
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'College') DROP TABLE College; 
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Student') DROP TABLE Student; 
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Apply') DROP TABLE Apply; 

---- Create Database. 
create table College(cName nvarchar(50), state text, enrollment int); 
create table Student(sID int, sName nvarchar(50), GPA real, sizeHS int); 
create table Apply(sID int, cName nvarchar(50), major nvarchar(50), decision text); 

Inner join

内联接（Inner join）是最常用的联接类型之一，它查询满足联接谓词的数据。

假设我们要查询申请表Apply中申请学校的相关信息，由于Apply表中包含学校名字我们并不能预知，所以我们可以根据cName来内联接（Inner join）表College和Apply，从而找到Apply表中包含学校的信息。

具体SQL代码如下：

代码如下:

 
---- Gets college information from college table 
---- bases on college name. 
SELECT DISTINCT College.cName, College.enrollment 
FROM College INNER JOIN 
Apply ON College.cName = Apply.cName 

join0

图1查询结果

cName

state

enrollment

Stanford

15000

Berkeley

36000

MIT

10000

Cornell

21000

Harvard

代码如下:

 
---- Gets all college information 
SELECT College.cName, College.state, College.enrollment, 
Apply.cName, Apply.major, Apply.decision 
FROM College LEFT OUTER JOIN 

join3 代码如下:

 
---- Gets all information from college and apply table. 
SELECT College.cName, College.state, College.enrollment, 
Apply.cName, Apply.major, Apply.decision 
FROM College FULL OUTER JOIN 
Apply ON College.cName = Apply.cName 

join4

图3 完整外部联接查询结果

现在我们获取了College和Apply的完全数据集，对于表中匹配的则有值，即使没有找到匹配cName的则以NULL值取代。

下表显示每种外部联接（outer join）匹配时保留数据行的情况：

联接类型	保留数据行
A left outer join B	all A rows
A right outer join B	all B rows
A full outer join B	all A and B rows

表2 外部联接保留数据行

完整外部联接（full outer join）满足交换律：“A full outer join B” 和 “B full outer join A” 是相等的。

Cross join
交叉联接（cross join）执行两个表的笛卡尔积（就是把表A和表B的数据进行一个N*M的组合）。也就是说，它匹配一个表与另一个表中的每一行；我们不能通过使用ON子句在交叉联接指定谓词，虽然我们可以使用WHERE子句来实现相同的结果，这是交叉联接基本上是作为一个内部联接了。

交叉联接相对于内部联接使用率较低，而且两个大表不应该进行交叉联接，因为这将导致一个非常昂贵的操作和一个非常大的结果集。

具体SQL代码如下：

代码如下:

 
---- College Cross join Apply. 
SELECT College.cName, College.state, College.enrollment, 
Apply.cName, Apply.major, Apply.decision 
FROM College 
CROSS JOIN Apply 

图4 College表和Apply表的行数

join6 代码如下:

 
---- Creates a function to get data from Apply base on sID. 
CREATE FUNCTION dbo.fn_Apply(@sID int) 
RETURNS @Apply TABLE (cName nvarchar(50), major nvarchar(50)) 
AS 
BEGIN 
INSERT @Apply SELECT cName, major FROM Apply where [sID] = @sID 
RETURN 
END 

---- Student cross apply function fn_Apply. 
SELECT Student.sName, Student.GPA, Student.sizeHS, 
cName, major 
FROM Student CROSS APPLY dbo.fn_Apply([sID]) 

我们也可以使用内部联接实现和Cross apply相同的查询功能，具体SQL代码如下：

代码如下:

 
---- Student INNER JOIN Apply bases on sID. 
SELECT Student.sName, Student.GPA, Student.sizeHS, 
cName, major 
FROM Student INNER JOIN [Apply] 
ON Student.sID = [Apply].sID 

join7 代码如下:

 
---- Student outer apply function fn_Apply. 
SELECT Student.sName, Student.GPA, Student.sizeHS, 
cName, major 
FROM Student OUTER APPLY dbo.fn_Apply([sID]) 

代码如下:

 
---- Student cross apply function fn_Apply. 
SET STATISTICS PROFILE ON 
SET STATISTICS TIME ON 

SELECT Student.sName, Student.GPA, Student.sizeHS, 
cName, major 
FROM Student CROSS APPLY dbo.fn_Apply([sID]) 

SET STATISTICS PROFILE OFF 
SET STATISTICS TIME OFF 

---- Student INNER JOIN Apply base on sID. 
SET STATISTICS PROFILE ON 
SET STATISTICS TIME ON 

SELECT Student.sName, Student.GPA, Student.sizeHS, 
cName, major 
FROM Student INNER JOIN [Apply] 
ON Student.sID = [Apply].sID 

SET STATISTICS PROFILE OFF 
SET STATISTICS TIME OFFCross apply 

查询执行时间:

CPU 时间= 0 毫秒，占用时间= 11 毫秒。

Inner join查询执行时间:

CPU 时间= 0 毫秒，占用时间= 4 毫秒。

join8 代码如下:

 
---- Student Semi-join Apply base on sID. 
SELECT Student.sName, Student.GPA, Student.sizeHS 
----[Apply].cName, [Apply].major 
FROM Student 
WHERE exists ( 
SELECT * 
from [Apply] 
where [Apply].sID = Student.sID 
) 

我们发现常用的EXISTS子句，原来是通过Left Semi Join实现的，所以说Semi-join在SQL Server中又许多使用场合。

join13

图9 查询结果

join12 代码如下:

 
---- Gets student still not apply for school. 
SELECT Student.sID, Student.sName, Student.GPA, Student.sizeHS 
----[Apply].cName, [Apply].major 
FROM Student 
WHERE NOT EXISTS ( 
SELECT * 
FROM [Apply] 
WHERE [Apply].sID = Student.sID 
) 

其实，我们常用的NOT EXISTS子句的实现是通过Anti-semi-join，通过执行计划我们发现在查找匹配sID时，SQL使用 Left Anti Semi Join进行查询。

join14

图11 查询结果

join15

图12 执行计划

1.1.3 总结
本文介绍了SQL中常用了联接查询方式：Inner join、Outer join、Cross join和Cross apply的使用场合和特性。

推荐阅读

数据库
推荐一个ASP的内容管理框架（ASP Nuke）的优势和适用场景

本文推荐了一个ASP的内容管理框架ASP Nuke，并介绍了其主要功能和特点。ASP Nuke支持文章新闻管理、投票、论坛等主要内容，并可以自定义模块。最新版本为0.8，虽然目前仍处于Alpha状态，但作者表示会继续更新完善。文章还分析了使用ASP的原因，包括ASP相对较小、易于部署和较简单等优势，适用于建立门户、网站的组织和小公司等场景。 ... [详细]

蜡笔小新 2023-12-14 18:11:11
数据库
Android 新闻App的本地服务器搭建教程

本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]

蜡笔小新 2023-12-14 17:15:19
数据库
postman测试登录后的接口_使用postman进行接口测试的方法(测试用户管理模块)

本文介绍了使用postman进行接口测试的方法，以测试用户管理模块为例。首先需要下载并安装postman，然后创建基本的请求并填写用户名密码进行登录测试。接下来可以进行用户查询和新增的测试。在新增时，可以进行异常测试，包括用户名超长和输入特殊字符的情况。通过测试发现后台没有对参数长度和特殊字符进行检查和过滤。 ... [详细]

蜡笔小新 2023-12-14 10:29:45
数据库
MysqlDump_mysqldump全库备份相关知识详解

本文详细介绍了MysqlDump和mysqldump进行全库备份的相关知识，包括备份命令的使用方法、my.cnf配置文件的设置、binlog日志的位置指定、增量恢复的方式以及适用于innodb引擎和myisam引擎的备份方法。对于需要进行数据库备份的用户来说，本文提供了一些有价值的参考内容。 ... [详细]

蜡笔小新 2023-12-14 10:03:27
join
使用 Ubuntu 中的 Python 获取浏览器历史记录

使用Ubuntu中的Python获取浏览器历史记录原文: ... [详细]

蜡笔小新 2023-12-14 08:57:59
数据库
PHP中的MySQL函数库及其常用函数介绍

本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-14 08:19:53
数据库
Oracle中tnsnames.ora的作用和配置方法

本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取，用于解析LOCAL_LISTENER，并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例，并展示了listener.ora文件的内容。 ... [详细]

蜡笔小新 2023-12-14 07:44:06
数据库
Spring特性实现接口多类的动态调用详解

本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]

蜡笔小新 2023-12-14 03:24:19
数据库
Java String与StringBuffer的区别及其应用场景

本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]

蜡笔小新 2023-12-13 19:21:06
sql
Oracle分析函数first_value()和last_value()的用法及原理

本文介绍了Oracle分析函数first_value()和last_value()的用法和原理，以及在查询销售记录日期和部门中的应用。通过示例和解释，详细说明了first_value()和last_value()的功能和不同之处。同时，对于last_value()的结果出现不一样的情况进行了解释，并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]

蜡笔小新 2023-12-13 19:07:23
sql
MyBatis错题分析解析及注意事项

本文对MyBatis的错题进行了分析和解析，同时介绍了使用MyBatis时需要注意的一些事项，如resultMap的使用、SqlSession和SqlSessionFactory的获取方式、动态SQL中的else元素和when元素的使用、resource属性和url属性的配置方式、typeAliases的使用方法等。同时还指出了在属性名与查询字段名不一致时需要使用resultMap进行结果映射，而不能使用resultType。 ... [详细]

蜡笔小新 2023-12-13 18:40:17
sql
ASP.NET Tips: 获取插入记录的ID的方法详解

本文详细介绍了在ASP.NET中获取插入记录的ID的几种方法，包括使用SCOPE_IDENTITY()和IDENT_CURRENT()函数，以及通过ExecuteReader方法执行SQL语句获取ID的步骤。同时，还提供了使用这些方法的示例代码和注意事项。对于需要获取表中最后一个插入操作所产生的ID或马上使用刚插入的新记录ID的开发者来说，本文提供了一些有用的技巧和建议。 ... [详细]

蜡笔小新 2023-12-13 17:03:18
数据库
VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]

蜡笔小新 2023-12-13 15:15:30
sql
SpringJdbcTemplate的使用详解

本文详细介绍了Spring的JdbcTemplate的使用方法，包括执行存储过程、存储函数的call()方法，执行任何SQL语句的execute()方法，单个更新和批量更新的update()和batchUpdate()方法，以及单查和列表查询的query()和queryForXXX()方法。提供了经过测试的API供使用。 ... [详细]

蜡笔小新 2023-12-13 14:27:11
数据库
高质量SQL书写的30条建议

本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]

蜡笔小新 2023-12-13 13:24:33

涵涵幸福2010_759

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章