SQL中的join操作总结（非常好）

最新推荐文章于 2023-12-13 20:07:16 发布
最新推荐文章于 2023-12-13 20:07:16 发布
阅读量3.8k 收藏 7
点赞数
分类专栏： 数据仓库与联机分析处理 文章标签： 数据库 sql 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 CA 15000
Berkeley CA 36000
MIT MA 10000
Cornell NY 21000
Harvard MA 29000

表1 College表中的数据

如上图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
        Apply ON College.cName = Apply.cName

join3

图2左联接查询结果

如上图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

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
join5

图4 College表和Apply表的行数

join6

图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

join7

图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])
join10

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

Cross apply查询执行时间:

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

Inner join查询执行时间:

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

join8

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

join13

图9 查询结果

join12

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

join14

图11 查询结果

join15

图12 执行计划

1.1.3 总结

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

系列博文导航

参考

SQL代码

mishidemudong
关注
  • 0
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Join的一些总结(实例)
09-11
Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联接等
SQL Join的一些总结
weixin_34189116的博客
03-27 371
1.1.1 摘要 Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据，这时我们应该考虑使用Join，因为Join具体联接表或函数进行查询的特性 本文将通过具体例子介绍SQL中的各种常用Join的特性和使用场合： 目录 Inner join O...
数据分析必备——SQL基础知识进阶版1.0
fengye8600的博客
11-18 280
从明天起，做一个幸福的人，喂马、劈柴，周游世界。从明天起，关心粮食和蔬菜，我有一所房子，面朝大海，春暖花开。——数据说·梦想季 导读：互联网发展给工程师们留下了很...
SQL合并两个表的数据
gyy12345gyy的博客
07-27 8014
1.表头不一致的情况，使用JOIN语句 left join (左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join (右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 inner join (等值连接) 只返回两个表中联结字段相等的行 例子：连接表一和表二 表一主键为组织机构代码，表二主键为zzjgdm 左连接 语句： SELECT * FROM `第一页` left join `税务` on 第一页.`组织机构代码`=...
SQL Join 用法详解
一路Coding
10-17 535
关于sql语句中的连接（join）关键字，是较为常用而又不太容易理解的关键字，下面这个例子给出了一个简单的解释 --建表table1,table2： create table table1(id int,name varchar(10)) create table table2(id int,score int) insert into table1 select 1,'lee' insert i...
sql语句查询加判断
admin123fy的博客
06-01 2401
SELECT sph.id, spa.user_id, spa.patient_id, sph.group_id, sph.org_code, dhmg.group_name, pi.name, pi.mobile, IF(sms.STATUS = 1, sms.maker_user_name, NULL) AS maker_user_name, IF(sms.STATUS = 1, sms.gmt_create
SQL Server 高级sql总结
12-14
本文将为您描述SQL Server 高级sql总结，具体操作方法: 　1、查询表中所有的数据 　———–select * from 表名 　———–select all id，name from 表名 　———–select id，name from 表名 　2、查询表中...
Oracle SQL连接查询总结.docx
05-22
Oracle SQL连接查询总结 缺省情况下是inner join,开发中使用的left join和right join属于outer join,另外outer join还包括full join.下面我通过图标让大家认识它们的区别。 现有两张表，Table A 是左边的表。Table ...
几种MySQL中的联接查询操作方法总结
12-15
而我们进行业务操作时，就需要在多个表之间，使用sql语句建立起关系，然后再进行各种sql操作。那么在使用sql写出各种操作时，如何使用sql语句，将多个表关联在一起，进行业务操作呢？而这篇文章，就对这个知识点进行...
SQL入门常见问题总结与实用技巧介绍.docx
最新发布
03-26
尽量避免不必要的子查询，采用连接JOIN操作提高效率。 注释习惯： 对复杂查询添加注释说明逻辑，便于后期维护和他人理解。 使用标准SQL注释符，如-- 单行注释 或 /* 多行注释 */。 查询性能优化 索引策略： 根据...
sql的连接
小虾米的专栏
08-02 596
SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。Join 和 Key有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。请看 "Persons" 表：Id_PLastNameFirstNameAddressCity1AdamsJo
数据库 | 辨析 cross join、inner join和outer join
易水卷长空的博客
03-20 5006
MySQL的内连接、自然连接、等值连接、交叉连接、左外连接、右外连接、全外连接的辨析。 inner join,outer join,natural join,where =,left join,right join,full join的区别
解析数据库联结：应用与实践中的 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 与 CROSS JOIN
qq_41586251的博客
12-13 1422
这篇文章深入探讨了数据库查询中常用的不同联结（JOIN）方式：INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 和 CROSS JOIN。详细介绍了每种联结的应用场景、基本语法以及在实际数据库查询中的使用方式，并通过示例展示了它们在不同数据表之间的联结效果。从关联数据的完整性到不同表之间的数据组合，本文全面剖析了这些联结操作的优势、用途以及应用技巧。深入理解这些联结方式将有助于提高对数据库关联数据处理的能力，为数据分析和决策提供可靠支持。
数据库中join的类型
TABE_的博客
12-30 2111
这里写目录标题一级目录二级目录三级目录内连接inner join用法实例左外连接LEFT [OUTER] JOIN用法实例右外连接RIGHT [OUTER] JOIN用法实例全连接FULL [OUTER] JOIN用法交叉连接CROSS JOIN用法自然连接NATURE JOIN用法自连接SELF JOIN左连接，右连接，内连接和全外连接的区别 一级目录 二级目录 三级目录 为了演示他们的区别，我们构建两个表，员工表emp，部门表dept，员工表中有21条记录，其中19条员工记录是有部门可查的，部门编号能
【MySQL】MySQL中的查询语句的详解----等值连接、左连接、右连接、全连接
热门推荐
wyz0516071128的博客
06-16 2万+
用两个表（t_worker,t_depart），关联字段t_worker.deptno和t_depart.deptno来演示一下MySQL的等值连接、左连接、右连接、全连接。数据库表：t_worker,t_depart 表1 表2 下面是有关等值连接、左连接、右连接、全连接相关概念 等值连接： 在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果...
SQL语句中JOIN的用法
zhangbeizhen18的博客
07-20 2万+
记录：256 写SQL最高境界：SELECT * FROM 表名。当然这是一句自嘲。探究一下SQL语句中JOIN的用法，直到经历这个场景，变得想验证一下究竟。 一、场景 把关系型数据库A中表TEST_TB01和TEST_TB02迁移到大数据平台M（MaxCompute大数据平台）。TEST_TB01单表1000万条记录，TEST_TB02单表80万条记录。 在关系型数据库中，TEST_TB01和TEST_TB02中有主键约束。在产生新增业务数据时，不会存在重复数据插入。但是，当数据迁移到大数据平
MySQL中的Join连接查询
BlueProtocolBlog
03-04 1942
我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法，ON语句的执行是在JOIN语句之前的，也就是说两张表数据行之间进行匹配的时候，会先判断数据行是否符合ON语句后面的条件，再决定是否JOIN,对参与 Join 操作的基表或视图进行过滤，之后再对两表进行 Join 操作，输出结果集。对于三表或多表 Join，则都是可以拆分为两表 Join 的方式进行处理，最先参与 Join 操作的两个表的 Join 的结果集，以表的形式参与后续的 Join 操作。我们来举个例子来简单理解笛卡尔积。
SQL使用join根据某列合并表
背水的博客
09-06 2183
数据库如下： 下表是用户(User)表 下表是用户组(UserGroup)表 在User表中UserGroupID我们需要通过UserGroup表获取UserGroupName用户组名，并且还要从User表获取Creater创建人和Changer修改人 这里中User表是基础表，其他表都是“服务”这张表，所以我们使用left join并且把User表放在最左边，语句如下： SELECT a....
SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
帅的刚刚好
11-01 5037
  Join 和 Key 有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。 数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。 请看 "Persons" 表： Id_P Las...
sql语句中where和join on
07-27
where和join on都是用于筛选数据的条件，但在SQL语句中的使用方式有所不同。 1. Where：Where子句用于在查询之后对结果集进行过滤。它可以在单个表查询或多个表查询中使用。Where子句通常在FROM子句之后和SELECT子句之前使用，用于指定条件。例如： ```sql SELECT column1, column2 FROM table1 WHERE condition; ``` 这里的condition是一个逻辑表达式，用于指定需要满足的条件。Where子句可以使用比较运算符（如=、<、>等）、逻辑运算符（如AND、OR）以及其他函数和操作符。 2. Join on：Join on子句用于将多个表连接起来，并指定连接条件。它通常在FROM子句之后使用，在WHERE子句之前。Join on子句指定了连接的列或条件，用于确定两个或多个表之间的关联关系。例如： ```sql SELECT column1, column2 FROM table1 JOIN table2 ON table1.column = table2.column; ``` 这里的ON子句指定了表table1和table2之间的连接条件，即它们的column列值相等。Join on子句可以使用比较运算符、逻辑运算符以及其他函数和操作符来指定连接条件。 总结： - Where子句用于在查询结果集中对数据进行筛选。 - Join on子句用于连接多个表，并指定连接条件。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值