SQL Join的一些总结(实例)
程序员文章站
2023-12-13 16:33:28
1.1.1 摘要 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
表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 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 毫秒。
如图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中又许多使用场合。
图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进行查询。
图12 执行计划
1.1.3 总结
本文介绍了sql中常用了联接查询方式：inner join、outer join、cross join和cross apply的使用场合和特性。
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 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 毫秒。
如图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中又许多使用场合。
图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进行查询。
图12 执行计划
1.1.3 总结
本文介绍了sql中常用了联接查询方式：inner join、outer join、cross join和cross apply的使用场合和特性。