您现在的位置是：网站首页> 编程资料编程资料
SQL Join的一些总结(实例)_MsSql_
2023-05-26
404人已围观
简介 SQL Join的一些总结(实例)_MsSql_
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
表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
如上图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
现在我们获取了College和Apply的完全数据集，对于表中匹配的则有值，即使没有找到匹配cName的则以NULL值取代。
下表显示每种外部联接（outer join）匹配时保留数据行的情况：
表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
现在我们对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
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])
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 STATIST
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
| 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
如上图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
现在我们获取了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表的行数
现在我们对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
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])
图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 STATIST
相关内容
- sqlserver 索引的一些总结_MsSql_
- SQL Server数据库入门学习总结_MsSql_
- sqlserver索引的原理及索引建立的注意事项小结_MsSql_
- 多列复合索引的使用 绕过微软sql server的一个缺陷_MsSql_
- 一列保存多个ID(将多个用逗号隔开的ID转换成用逗号隔开的名称)_MsSql_
- SQL语句分组获取记录的第一条数据的方法_MsSql_
- Sql Server 2012 转换函数的比较(Cast、Convert和Parse)_MsSql_
- CREATE FUNCTION sqlserver用户定义函数_MsSql_
- SQL Server字符串切割函数_MsSql_
- sqlserver主键设计的注意点_MsSql_
点击排行
本栏推荐
