10、SQL Server 使用联接和联合合并数据之联接

使用联接和联合合并数据之联接

：首先将两个数据集相乘，然后对返回的结果进行限制，从而只返回两个数据集的交集。

：旨在水平地合并两个数据集，并通过将一个数据源中的行与另一个数据源的行匹配，

来生成新的结果集。

SQL 支持多种联接，它们决定了如何选择两个数据集中的行。

连接类型	查询设计器符号	定义
内联接		只包含匹配的行
左外联接		包含左边表的全部行，加上 匹配的行
右外联接		包含右表的全部行，加上 匹配的行
全外联接		包含两个表的全部行， 不管在另一个表是否存在匹配的行
θ(theta)联接		使用非相等条件如：>、< 、>=等，来匹配两个表中的行
交叉联接	没有联接线	生成笛卡尔积，不使用任何匹配或限制条件，而认为一个数据源中的每个行与另一个数据库的每个行都匹配。

一、内联接(inner join)

 在关系代数上也称为自然连接。只返回两个数据集之间的匹配的行。

1、在SQL代码中建立内联接

基本语法 ：

select columns from  table1  inner join  table2 on conditions

注：inner可省略，对Table1表中的行SQL都尝试在table2中查找满足条件conditions

条件，与之匹配的行。

在早期的ANSI SQL89中还有一种可代替语法：

select  colmuns  from  table1，table2  where  conditions

如：select * from bumen inner join zhiwei on bumen.id=zhiwei.bmid

    select * from bumen,zhiwei where bumen.id=zhiwei.bmid

两个查询的结果集一致。

2、返回的行数

结果集中的行数有可能增加或减少，这取决于每个数据源匹配的行数及联接类型。

如果一个表中的某行与另一个表中的多行相匹配，则在联接结果中，每次匹配都将

产生对应的一行数据

3、多表联接

联接的数据源多于2个。

二、外联接

外联接扩展了内联接，它还返回左边或右边的数据集中不匹配的数据行。

基本语法：

在join前面使用关键字Left  Outer 或 Right  Outer 声明的。

注：Outer可省略。其中这两个联接，分别称为左外联接和右外联接。

1、外联接与可选的外键 

通常在从表中有引用主表的外键约束，且允许外键列为空值的时候，使用

外联接。(这种可选的外键意味着，如果从表中的行引用了主表中的行，后者必须存在

，然而，从表中的行没有引用主表中的行也是完全合法的。)

2、全外联接

 在Join前加上 full outer

：返回两个数据集中的所有数据，而不管它们是否有匹配的行。

（其中两个表中匹配的行一起排列在结果集中的同一行。）

从功能上说，它等价于将这两个数据集进行左外联接和右外联接，然后对得到的结果

执行消除重复行的联合操作。

注：全外联接非常适用于找出所有的数据。

3、在外联接中设置条件

使用内联接时，在Join子句后的on加条件，还是对结果集进行筛选的where

指定条件时的效果是完全相同的（可以通过查看执行计划查看 Ctrl+L）。

但是对于外联接来说情况并非如此。

1、在Join中指定条件时(使用on conditions)，SQL Server将返回外联接表的

所有行，然后根据指定的条件决定返回第二个表的哪些行

2、如果在where子句中指定条件，SQL Server将首先执行联接操作生成中间

结果集，然后根据where子句对联接后的行进行筛选。

三、自联接

将表与它自己进行联接。

这种一元联接通常用于从自反关系(也称递归关系中)提取数据。

要点：使用范围变量或表别名再次引用同一个表。

如：Select B.id,B.name from Person A inner join Person B on A.montherid=B.id

四、交叉联接

对两个源表执行纯关系代数乘法。

返回两个数据源中的行的所有可能的组合。

结果集：笛卡尔乘积

基本语法：Cross  join  并省略on条件。

如：select * from bumen cross join zhiwei 

五、特殊联接

1、θ(theta)联接

：使用非等on条件的联接。

如：select * from bumen inner join zhiwei on bumen.id>zhiwei.bmid

2、多条件联接

on conditions and conditons or conditions

如：select * from bumen inner join zhiwei on bumen.id=zhiwei.bmid and bumen.id=6

3、非键联接

并非只能使用主键和外键来进行联接，实际上，可以根据任何列来匹配两个数据

源中的行，只要这些列的数据类型是兼容的，且包含的数据是匹配的。

注：特殊联接经常同子查询结合使用。

六、差集查询

：它从被除数中删除与除数的内容，只留下不同的内容。

（它根据联接条件找出两个集合之间的差异）没有基本语法，只能组合普通的联接

和查询实现。

基本实现方法 ：1、使用外联接 2、使用 Where not in 和子查询

 1、使用外联接

由于外联接返回不匹配的行，因此，可以通过使用外联接并对第二个数据集的主键

使用 is  null 条件来创建差集查询，这将返回表one中所有这样的行，

（即在表two中没有匹配的行。）

如：select * from bumen left outer join zhiwei on bumen.id=zhiwei.bmid 

where zhiwei.bmid is null

结果：

id bmname isdelete id    zwname bmid isdelete

2 后勤部3 1    NULL NULL NULL NULL

5 后勤部 0    NULL NULL NULL NULL

分析：

1、首先使用左外联接查询，查出对左表的每行，满足匹配的右表的行

2、对于左表中的行，如果在右表中没有匹配的行，也显示出来，而且右表中本应

有的匹配数据，全用NULL填充。

3、对这个结果集使用 where zhiwei.bmid is null，对结果集中的bmid列

进行筛选，只有bmid is null时，才返回，这也就是在职位表中没有与之匹配

的bmid。(最终结果是：没有职位存在的部门信息)

2、使用Where not in 和子查询

 如：select * from bumen where id not in (select bmid from zhiwei)

结果：

id bmname isdelete

2 后勤部3 1

5 后勤部 0