首页 > 代码库 > 详解T-SQL的联接机制

详解T-SQL的联接机制

T-SQL的联接分三种类型:

交叉联接 ( cross join )

内部联接    (inner join)

外部联接    (outer join)

每种联接在逻辑上经历不同的阶段,分为笛卡尔乘积、筛选、添加外部行,如图:

联接类型

阶段

交叉联接

笛卡儿乘积

内部联接

笛卡尔乘积、筛选

外部联接

笛卡儿乘积、筛选、添加外部行

接下来将以例子逐个讲解三种联接,假设有一张表a, 如下:

id
--
1
2
3
4

和一张表b ,如下:

id
--
3
4
5
6

 

1、交叉联接是最简单的联接,它只进行笛卡儿乘积。如果表a有x条记录,表b有y条记录,cross join 之后会生成 x*y条记录。 

select a.id as aId,b.id as bId from a cross join b 

aId bId
--- ---
1 3
2 3
3 3
4 3
1 4
2 4
3 4
4 4
1 5
2 5
3 5
4 5
1 6
2 6
3 6
4 6

 

2、内联接进行了笛卡儿乘积筛选两个阶段。在写法上,inner join 的 inner是可选的,并且在on 后面指定筛选的条件(条件可以相等也可以不相等)。

select a.id as aId, b.id as bId from a

  inner join b on a.id=b.id

aId bId
--- ---
3 3
4 4

select a.id as aId,b.id as bId from a

 join b on a.id>b.id

aId bId
--- ---
4 3

如上结果,on指定的条件把不符合的记录给过滤掉了。

 

3、外部联接是指左外联接(left outer join)、右外联接(right outer join)、全外联接(full outer join)。写法上,outer 是可选的。 

    外联接进行了笛卡尔乘积筛选、和添加外部行三个阶段。相对于内联接,外联接多了一个添加外部行的阶段,也就是说在进行完筛选后,把该保留的记录添加回输出表中。

 

1) 所谓左外联接就是保留左侧表的所有记录,如有不匹配列的记录,则用null 填充

select a.id as aId,b.id as bId from a 

   left outer join b on a.id=b.id 

aId bId 

--- ------
1 (null)
2 (null)
3 3
4 4

2) 右外联接就是保留右侧表的所有记录,如有不匹配列的记录,则用null 填充

select a.id as aId,b.id as bId from a 

   right join b on a.id=b.id 

aId bId
------ ---
3 3
4 4
(null) 5
(null) 6

3) 全外联接就是保留两侧表的所有记录,如有不匹配列的记录,则用null 填充

select a.id as aId,b.id as bId from a 

   full join b on a.id=b.id 

aId bId
------ ------
1 (null)
2 (null)
3 3
4 4
(null) 5  
(null) 6

 

注:以上例子讲的都是两张表的联接。如是多表联接,则从左侧开始,每个联接所生成的输出表是下一个联接的左侧表。

 

详解T-SQL的联接机制