SqlServer 表连接教程(问题解析)

1.2 本篇文章内容概要

1.3 本篇文章内容概括

在SQL语句中，关于表连接，若按照表的数量来划分，可以划分为单表连接、两表连接和两表以上连接，在本篇文章中，主要讲解两表连接，其他多表连接原理一样。

关于表连接有很多种类，本文主要讲解交叉连接，内连接，外连接（左外部连接，右外部连接，全连接），自连接。

1.4 本章测试样表和Sql

业务场景：有两张表，分为为顾客表Customers和顾客订单表Orders，SQL语句分别如下：

创建Customes并初始化

--CREATE TABLE Customers
CREATE TABLE Customers
(
  CustID VARCHAR(50) NOT NULL, --顾客ID
  CustName VARCHAR(50),--顾客姓名
  CustCompany VARCHAR(50) --顾客公司
)

--Initial Customers

INSERT INTO Customers VALUES('SXN-DD-01','赵武','A')
INSERT INTO Customers VALUES('SXN-DD-02','刘杨','B')
INSERT INTO Customers VALUES('SXN-DD-03','张永为','C')
INSERT INTO Customers VALUES('SXN-DD-04','李龙飞','D')
INSERT INTO Customers VALUES('SXN-FF-01','邓华','E')
INSERT INTO Customers VALUES('SXN-HH-01','张涛明','F')

查询结果为：

创建Order表并初始化

CREATE TABLE Orders
(
 CustID VARCHAR(50) NOT NULL, --顾客ID
 OrdetID VARCHAR(50) --订单ID
)

--Initial Orders

INSERT INTO Orders VALUES('SXN-DD-01','SCCCCFFFFFSSOX002')
INSERT INTO Orders VALUES('SXN-DD-02','SCCCCFFFFFSSOX0X2')
INSERT INTO Orders VALUES('SXN-DD-03','')
INSERT INTO Orders VALUES('SXN-DD-04','')
INSERT INTO Orders VALUES('SXN-DD-05','SCCCCFFFFFSSOX0H2')
INSERT INTO Orders VALUES('SXN-DD-06','')

查询结果为：

2 问题引入

Q1：写一个查询，生成从1到1000的整数序列。

3 交叉连接

3.1 SQL示例及示例结果

 SELECT C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID
FROM Customers AS C
CROSS JOIN Orders AS O

结果：

3.2 示例结果分析

交叉连接使用关键字CROSS JOIN进行查询，查询的结果为笛卡儿积，从如上结果可以看出，查询出的结果共有36行数据，因为Customers表和Orders表分别有6条记录，6X6=36；

3.3 小结

a.在逻辑上，交叉连接是一种最简单的联接;
b.交叉连接只实现一个逻辑处理步骤―――笛卡儿积;
c.操作：对输入的两个表进行操作，把它们连接起来，生成两者的笛卡儿积，即将一个输入表的每行与另一个表的所以行进行匹配，如果一个表有m行，而另一个表有n行，将得到m x n行的接果集;
d.结构：
  SELECT  tb1.tb1ConumName,tb2.tb2ConumName
  FROM table1 AS tb1
  CROSS JOIN table2 AS tb2
e.交叉连接使用的关键字：CROSS JOIN ;
f.交叉连接生成的接果集是一个虚拟表，虚拟表中的各列直接源于参与连接的两个表;

4 内连接

4.1 SQL示例及示例结果

SELECT C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID
 FROM Customers AS C
 INNER JOIN Orders AS O
 ON C.CustID=O.CustID

结果：

4.2 示例结果分析

内连接在交叉连接的基础上外加过滤条件ON，如上例子中用Customers.CustID=Orders.CustID作为过滤条件，结果显而易见。

4.3 小结

内联接规则为笛卡尔积+用户谓词过滤：它首先像交叉连接一样，对两个输入表进行笛卡尔积运算，然后根据用户指定的谓词对结果进行过滤;

5 外连接

5.1 SQL示例及示例结果(只分析左外部连接，因为右连接和全连接原理也是一样的)

SELECT C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID
 FROM Customers AS C
 LEFT OUTER JOIN Orders AS O
 ON C.CustID=O.CustID

结果：

5.2 示例结果分析

如上以Cutomers表作为左保留表，连接右表Orders列CustID缺少SXN-FF-01和SXN-HH-01，为了以左保留表为基准，用NULL占位符来填充。

5.3 小结

a.外连接：笛卡儿积+ON过滤+外部行;
b.在外连接中，要把一个表标记为“保留的”表，可以在表名之间使用关键字LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN，其中OUTER关键字是可选的。LEFT关键字表示左边表的行是保留的，RIGHT关键字表示右边表的行是保留的，而FULL关键字则表示左右两边表的行都是保留的;
c.外连接的第三个逻辑查询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行，再把这些行添加到连接的前两个步骤生成的结果中。对于来自连接的非保留表的那些列，追加的外不行中的这些列则用NULL作为占位符;
d.从外连接保留表的角度来看，可以认为外连接结果中的数据行包括两种内部行和外部行。内部行是指按照ON子句中的条件能在连接的另一边找到匹配的那些行；而外部行则是指找不到匹配的那些行。内连接只返回内部行，外连接同时返回内部行和外部行;
e.使用外连接时，到底是在查询的ON子句中，还是在WHERE子句中指定连结条件？从外连接保留表中的行来考虑该问题，ON子句中的过滤条件不是最终的，换句话说，ON子句中的条件并不能最终决定保留表中的部分行是否会在结果中出现，而只是判断是否能够匹配另一边表中的某些行。所以，当需要表达一个非最终的条件时（即这个条件只决定哪些行可以匹配非保留表），就在ON子句中指定连接条件，当在生成外部行以后，要运用过滤器，而且希望过滤条件是最终的，就应该在WHERE子句中指定条件;

6自连接

6.1 SQL示例及示例结果

SELECT C1.CustID AS C1CustID,C1.CustName AS C1CustName,C1.CustCompany AS C1CustCompany,C2.CustID,C2.CustName,C2.CustCompany
 FROM Customers AS C1
 JOIN Customers AS C2
 ON C1.CustID=C2.CustID

结果：

6.2 示例结果分析

如上例子为自连接在内连接中的运用，在其他连接中的运用就不举例子了，比较简单。

6.3 小结

a.自连接为单个表取不同的别名，通过别名来连接；
b.自连接可以用于其它连接；
b.自连接可以看作交叉连接、内连接、外连接等连接的一个特例;

7问题答案

Q1：KEY

CREATE TABLE Digits
( 
 digit int not null primary key
)
--Initial testing data for Digits
INSERT INTO Digits VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

--Query

SELECT D3.digit*100+D2.digit*10+D1.digit+1 AS n
FROM Digits AS D1
CROSS JOIN Digits AS D2
CROSS JOIN Digits AS D3
ORDER BY n

8参考文献

【01】Microsoft SqlServer 2008技术内幕:T-SQL语言基础

【02】Microsoft SqlServer 2008技术内幕:T-SQL查询

总结