SQL Join的一些总结(实例)

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查询结果
表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左联接查询结果

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

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

现在我们获取了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

图4 College表和Apply表的行数

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