讲师中心 微信公众号

工具推荐

视频效率加速
剧云
剧云

中文短剧剧本制作平台

立刻MV
立刻MV

低成本歌曲 MV、视频生成

updream

一键生成高质量推广成片

Seko
Seko

AI短剧一键生成成片

SQL Join的一些总结(实例)

秋晨酱_9230

秋晨酱_9230

发布时间:2016-06-07 18:07:55

|

1455人浏览过

|

来源于php中文网

原创

Join是关系型数据库系统的重要操作之一,SQL Server中包含的常用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

1318.png

图1查询结果
表1 College表中的数据 

cName state enrollment
Stanford CA 15000
Berkeley CA 36000
MIT MA 10000
Cornell NY 21000
Harvard MA 29000


如上图1所示,我们把Apply表包含的学校信息查询出来了,由于Harvard并没有被查询出来,所以我们知道暂时还没有学生申请Harvard。

内联接(Inner join)满足交换律:“A inner join B” 和 “B inner join A” 是相等的。

Outer join
假设我们想看到所有学校信息;即使是那些没有申请的学校(如:Harvard),这时我们可以使用外部联接(Outer join)进行查询。由于外部联接保存一个或两个输入表的所有行,即使无法找到匹配联接谓词的行。

具体SQL代码如下:
代码如下:

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

1319.png

图3左联接查询结果

如上图3所示:由于在Apply表中并没有学生申请Harvard,但是我们通过左联接(left outer join)把所有学校信息查询出来了。

由于左联接(left outer join)产生表College的完全集,而Apply表中匹配的则有值,而不匹配的则以NULL值取代,所以我们知道Apply表中没有学生申请Harvard。

通过左联接查询我们可以获取College的完全集,假设现在我们既要获取College的完全集又要获取Apply的完全集,那么我们可以考虑使用完整外部联接(full outer join)。使用完整外部联接,我们可以查询所有的学校,不管它们是否匹配联接谓词:
代码如下:

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

1320.png

图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

1321.png

图4 College表和Apply表的行数

1322.png

图5 交叉联接

现在我们对College和Apply表进行交叉联接,而且生成数据行为College和Apply表行数的笛卡尔积即5 * 20 = 100。

Cross apply
在SQL Server 2005中提供了Cross apply使表可以和表值函数(table-valued functions TVF‘s)结果进行join查询。例如,现在我们想通过函数的结果值和表Student进行查询,这时我们可以使用Cross apply进行查询:
代码如下:

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

1323.png

DBeaver Community 26.2.1
DBeaver Community 26.2.1

DBeaver Community 26.2.1 Windows x64 官方安装包,适用于数据库连接、SQL 开发、数据浏览和管理。

下载

图6 Cross apply查询

Outer apply
在介绍Cross apply和Outer join之后,现在让我们理解Out apply也就不难了,Outer apply使表可以和表值函数(table-valued functions TVF‘s)结果进行join查询,找到匹配值则有值,没有找到匹配值则以NULL表示。
代码如下:

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

1324.png

图7 Outer apply查询

Inner Join和Cross apply的区别

首先我们知道Inner join是表和表的联接查询,而Cross apply是表和表值函数的联接查询,在前面Cross apply例子中,我们也可以通过Inner join实现相同的查询。
代码如下:

---- 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 毫秒。

1325.png

图8 执行计划

如图8所示:Cross apply首先执行TVF(table-valued functions),然后对表Studnet进行全表扫描,接着通过遍历sID查找匹配值。

Inner join对表Student和Apply进行全表扫描,然后通过哈希匹配查找匹配的sID值。

通过以上的SQL执行时间和执行计划,我们能不能说Inner join比Cross apply好呢?答案是否定的,如果表的数据量很大,那么Inner join的全表扫描耗费时间和CPU资源就增加了(可通过数据量大的表进行测试)。

虽然大多数采用Cross apply实现的查询,可以通过Inner join实现,但Cross apply可能产生更好的执行计划和更佳的性能,因为它可以在联接执行之前限制集合加入。

Semi-join和Anti-semi-join

Semi-join从一个表中返回的行与另一个表中数据行进行不完全联接查询(查找到匹配的数据行就返回,不再继续查找)。

Anti-semi-join从一个表中返回的行与另一个表中数据行进行不完全联接查询,然后返回不匹配的数据。

不同于其他的联接运算,Semi-join和Anti-semi-join没有明确的语法来实现,但Semi-join和Anti-semi-join在SQL Server中有多种应用场合。我们可以使用EXISTS子来实现Semi-join查询,Not EXISTS来实现Anti-semi-join。现在让我们通过具体的例子说明吧!

假设要求我们找出Apply和Student表中sID匹配的学生信息,这和前面的Inner join查询结果将一样,具体SQL代码如下:
代码如下:

---- 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中又许多使用场合。

1326.png

图9 查询结果

1327.png



图10 执行计划

现在要求我们找出还没有申请学校的学生信息,这时我们立刻反应可以使用NOT EXISTS子句来实现该查询,具体SQL代码如下:
代码如下:

---- 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进行查询。

1328.png

图11 查询结果

1329.png


图12 执行计划

1.1.3 总结
本文介绍了SQL中常用了联接查询方式:Inner join、Outer join、Cross join和Cross apply的使用场合和特性,更多相关内容请关注PHP中文网(www.php.cn)!


热门AI工具

更多
咔片AIPPT

一款在线AI演示文稿制作工具,可根据主题和内容需求辅助生成PPT结构与页面,提高演示材料制作效率。

超级简历WonderCV

一款AI办公效率工具,主要用于免费求职简历模版下载制作,应届生职场人必备简历制作神器,适合需要提升相关任务效率的用户。

DeepSeek

DeepSeek是一款面向对话、写作、编程和推理场景的AI大模型工具。

笔灵降AI

一款AI论文写作工具,主要用于论文降AI神器,适配知网及维普!一键降至安全线,100%保留原文格式;无口语化问题,文风更学术,降后字数控制最佳!,适合需要提升相关任务效率的用户。

PixTV
PixTV Hot

PixTV是一款面向AIGC内容创作的AI视频生成工具。

豆包大模型

豆包大模型是一款由字节跳动推出的企业级大语言模型服务平台。

Seko
Seko Hot

一款AI视频创作工具,主要用于商汤科技推出的创编一体的AI短视频创作Agent,适合需要提升相关任务效率的用户。

WorkBuddy

一款AI办公效率工具,主要用于腾讯云推出的AI原生桌面智能体工作台,适合需要提升相关任务效率的用户。

SoundView
SoundView Hot

SoundView是一款面向跨境电商的视频翻译、配音和本地化 AI 创作平台。

相关专题

更多
Nginx CVE-2026-123007排查与防护指南
Nginx CVE-2026-123007排查与防护指南

Nginx HTTP/2漏洞该怎么修复?本专题围绕CVE-2026-123007,介绍安全版本升级、模块兼容检查和配置恢复方法,以及日志排查、权限设置等后续加固操作,方便你按实际部署情况查找对应教程。

0

2026.11.20

GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

40

2026.11.17

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

60

2026.11.17

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

80

2026.11.17

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

20

2026.11.17

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

80

2026.11.17

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

40

2026.11.17

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

180

2026.11.12

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

140

2026.11.12

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Navicat Monitor 3 用户手册
Navicat Monitor 3 用户手册

共0课时 | 0人学习

SQLAlchemy 2.1 官方统一教程
SQLAlchemy 2.1 官方统一教程

共0课时 | 0人学习

DBeaver生态工具dbvr官方手册
DBeaver生态工具dbvr官方手册

共0课时 | 0人学习

关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn