join left join right join outer join 以及笛卡儿积

join 一共有四个  左右 内外

join       inner join

left join      outer join

right join   outer join

full join      outer join

举例：

A表

1    

2     

B表

1    

3    

A  join B     

1     1

A left join B

1  1

2  null

A right join B

1  1

null 3

A full join B

1  1

2  null

null 3

join = inner join

Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据，这时我们应该考虑使用Join，因为Join具体联接表或函数进行查询的特性

本文将通过具体例子介绍SQL中的各种常用Join的特性和使用场合：

目录

• Inner join
• Outer 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查询结果

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

图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 

图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

图4 College表和Apply表的行数

图5 交叉联接

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