T-SQL之连接查询、子查询、联合查询和交叉表查询

1.连接查询

内连接

内连接时用比较运算符比较要连接列的值的连接。

1.等值查询

用来连接两个表的条件称为连接条件或连接谓词，其一般格式如下：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

【提示】

当比较运算符为“=”时，称为等值连接。如下：

[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。

2.非等值连接

使用>、<、>=、<=等运算符作为连接条件的连接称为非等值连接。但在实际应用中很少使用非等值连接，并且通常非等值连接只有与自身连接同时使用才有意义。

3.自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，这种连接称为表的自身连接。自身连接一般很少用于查询数据，主要用于INSERT、UPDATE语句中对一个表中满足特定条件的行进行操作的情况。

外连接

在通常的连接操作中，只有满足连接条件的记录才能作为结果输出。

1.左外连接

左外连接包括第一个命名表（“左表”，出现在JOIN子句的最左边）中的所有行，但不包括右表中的不满足条件的行。

2.右外连接

右外连接包括第二个命名表（“右表”，出现在JOIN子句的最右边）中的所有行，但不包括左表中不满足条件的行。

3.完整外部连接

完整外部连接将包括所有连接表中的所有行，不论它们是否匹配。

交叉连接

连接运算中还有一种特殊情况，即卡氏积连接，卡氏积是不带连接谓词的连接。两个表的卡氏积即是两表中记录的交叉乘积，即其中以表中的每一记录都要与另一个表中的每一记录做拼接，因此结果表往往很大。

2.子查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语中的查询称为嵌套查询或子查询。

子查询类型

1.使用IN或NOT IN的子查询

带有IN谓词的子查询是指父查询与子查询之间用IN进行连接，判断某个属性列指是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以谓词IN是嵌套查询中最经常使用的谓词。

【提示】

• 实现同一个查询可以采用多种方法，当然不同过的方法其执行效率可能会有差别，甚至会差别很大。数据库用户可以根据自己的需要进行合理的选择。
• 连接总是可以表示为子查询，子查询经常（但不总是）可以表示为连接。
• 在一些必须检查存在性的情况中，使用连接会产生更好的性能。

2.使用比较运算符的子查询

带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询放回的是单值时，可以用>、<、=、>=、<=、!=或<>等比较运算符。单值情况下使用=，多值情况下使用IN或NOT IN谓词。

3.使用ANY或ALL的子查询

子查询返回单值时可以用比较运算符，而使用ANY或ALL谓词时则必须同时使用比较运算符，其含义如下表：

带有ANY或ALL谓词的相关连词

连词	含义
>ANY	大于子查询结果中的某个值（大于最小）
<ANY	小于子查询结果中的某个值（小于最大）
>=ANY	大于等于子查询结果中的某个值
<=ANY	小于等于子查询结果中的某个值
=ANY	等于子查询结果中的某个值
!=ANY或<>ANY	不等于子查询结果中的某个值
>ALL	大于子查询结果中的所有值（大于最大的）
<ALL	小于子查询结果中的所有值（小于最小的）
>=ALL	大于等于子查询结果中的所有值
<=ALL	小于等于子查询结果中的所有值
=ALL	等于子查询结果中的所有值（通常没有实际意义）
!=ALL或<>ALL	不等于子查询结果中的任何一个值

【提示】

• 事实上，用聚合函数实现子查询通常比直接用ANY或ALL查询效率要高
• 如果将上例中的ALL改为ANY，即只需要比最大的小即可。反过来，如果是大于ANY，则只需要大于最小的即可。

4.使用EXISTS的子查询

EXISTS代表存在量词“彐”。带有EXISTS谓词的子查询不返回任何实际数据，它只产生逻辑真值“True”或逻辑假值“False”。

【提示】

• 使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。
• 有EXISTS引出的子查询，其目标列表达式通常都用“*”，因为带EXISTS的子查询只返回真值或假值，给出列名也没有实际意义。
• 这类查询与前面的不相关子查询有一个明显区别，即子查询的查询条件依赖于外层父查询的某个属性值，我们称这类查询为相关子查询。

6.INSERT语句中的子查询

插入子查询结果的INSERT语句的基本语句格式如下所示：

INSERT
INTO<表名>[(属性列1)[,<属性列2>...]]
子查询

其功能是一次将子查询的结果全部插入到指定的表中。

【提示】

• 目标子句中的列必须与被插入表中所指定的被插入列一一对应，名称可以不同，但类型必须一致。
• 必须首先建立好目标表。

7.UPDATE语句中的子查询

子查询也可以嵌套在UPDATE语句中，用于构造执行修改操作的条件。

8.子查询规则

子查询也是使用SELECT语句组成的，所以在使用SELECT语句时应注意的问题也同样适用于子查询。同时子查询还受一下条件的限制：

（1）通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称。

（2）如果外部查询的WHERE子句包括某个列名，则该字句必须与子查询选择列表中的该列兼容。

（3）子查询的选择列表中不允许出现ntext、text和image数据类型。

（4）无修改的比较运算符引入的子查询不能包括GROUP BY和HAVING子句。

（5）包括GROUP BY的子查询不能使用DISTINCT关键字。

（6）不能指定COMPUTE和INTO子句。

（7）只有同时指定了TOP，才可以指定ORDER BY。

（8）由子查询创建的视图不能更新。

（9）通过EXISTS引入的子查询的选择列表由星号（*）组成，而不使用单个列名。

（10）当=、!=、<、<=、>或>=用在主查询中时，ORDER BY子句和GROUP BY子句不能用在内层查询中，因为内层查询返回一个以上的值不可被外层查询处理。

联合查询

每一个SELECT语句都能获得一个或一组记录。若要把多个SELECT语句的结果合并为一个结果，则可用集合操作来完成。集合操作主要包括并操作UNION、交操作INTERSECT和差操作MINUS。

使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的记录。默认情况下，使用UNION运算符的结果集会从所联合的查询中删除重复行，而如果使用了ALL子句，则重复行也会显示出来。

【提示】

• 参加UNION在操作的各数据项（字段名、算数表达式、聚合函数）数目必须相同。
• 对应项的数据类型必须相同，或者可以进行显式或隐式转换。
• 各语句中对应的结果集列出现的顺序必须相同。

交叉表查询

1.PIVOT

PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。

PIVOT指定在FROM子句中，其基本语句格式如下：

SELECT<不旋踵的列>,
    [第1个旋转列]AS<别名>,
    [第2个旋转列]AS<别名>,
    ...
    [最后一个旋转列]AS<别名>,
FROM
    (<SELECT 生成的数据结果集>)
    AS<查询结果集的别名>
PIVOT
(
    <聚合函数>(<聚合列>)
FOR
[<被转换为表头的列>]
  IN([第1个旋转列],[第2个旋转列],
  ...[最后一个旋转列])
)AS<旋转表的别名>
<可选的ORDER BY子句>;

【提示】

• PIVOT和UNPIVOT运算符需要SQL Server 2005以上版本才可以支持。
• 数据库的兼容级别必须在90以上，可以使用EXEC sp_dbcmptlevel修改数据库兼容级别。

2.UNPIVOT

UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。但UNPIVOT不完全是PIVOT的逆操作。PIVOT会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而UNPIVOT不会重现原始表值表达式的结果，因为行已经被合并了。另外，UNPIVOT的输入中的空值不会显示在输出中，而在执行PIVOT操作之前输入中可能有原始的空值。

UNPIVOT指定在FROM子句中，其基本语句格式如下：

SELECT<不旋转的列>,<旋转后的列名>,<待旋转列名下的列值列名>
FROM
    (<SELECT 生成的数据结果集>)
    AS<查询结果集的别名>
UNPIVOT
(
    <待旋转列名下的列值列名>
FOR
[<旋转后的列名>]
    IN([第1个待旋转列的列名],[第2个待旋转列的列名],
    ...[最后一个待旋转列的列名])
)AS<旋转表的别名>
<可选的ORDER BY子句>;