SQL高级查询方法

正文共：5024 字 2 图  预计阅读时间：14 分钟

前文推送

1. SQL Server入门

2. Transact-SQL基础(上)

3. Transact-SQL基础(下)

4. SQL笔试50题(上)

5. SQL笔试50题(下)

6. SQL基础查询方法

本文目录：

• 4.8 子查询 subquery

• 4.9 联接 join

• 4.10 UNION运算符

• 4.11 EXCEPT和INTERSECT半联接

• 4.12 公用表表达式 WITH

4.8 子查询 subquery

子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。

子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

有三种基本的子查询。它们是：

• 在通过 IN 或由 ANY 或 ALL 修改的比较运算符引入的列表上操作。WHERE expression [NOT] IN (subquery)

• 通过未修改的比较运算符引入且必须返回单个值。WHERE expression comparison_operator [ANY | ALL] (subquery)

• 通过 EXISTS 引入的存在测试。WHERE [NOT] EXISTS (subquery)

许多包含子查询的 Transact-SQL 语句都可以改用联接表示。其他问题只能通过子查询提出。

在 Transact-SQL 中，包含子查询的语句和语义上等效的不包含子查询的语句（即联接的方式）在性能上通常没有差别。但是，在一些必须检查存在性的情况中，使用联接会产生更好的性能。否则，为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。所以在这些情况下，联接方式会产生更好的效果。

子查询的 SELECT 查询总是使用 圆括号括起来。它不能包含 COMPUTE 或 FOR BROWSE 子句，如果同时指定了 TOP 子句，则只能包含 ORDER BY 子句。

子查询受下列 限制的制约：

• 通过比较运算符引入的子查询选择列表只能包括一个表达式或列名称（对 SELECT * 执行的 EXISTS 或对列表执行的 IN 子查询除外）。

• 如果外部查询的 WHERE 子句包括列名称，它必须与子查询选择列表中的列是联接兼容的。

• ntext、text 和 image 数据类型不能用在子查询的选择列表中。

• 由于必须返回单个值，所以由未修改的比较运算符（即后面未跟关键字 ANY 或 ALL 的运算符）引入的子查询不能包含 GROUP BY 和 HAVING 子句。

• 包含 GROUP BY 的子查询不能使用 DISTINCT 关键字。

• 不能指定 COMPUTE 和 INTO 子句。

• 只有指定了 TOP 时才能指定 ORDER BY。

• 不能更新使用子查询创建的视图。

• 按照惯例，由 EXISTS 引入的子查询的选择列表有一个星号 (*)，而不是单个列名。因为由 EXISTS 引入的子查询创建了存在测试并返回 TRUE 或 FALSE 而非数据，所以其规则与标准选择列表的规则相同。

子查询的例子可以参考笔试题中的例子，SQL笔试50题（上），SQL笔试50题（下）

4.9 联接 join

通过联接，可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。

联接条件可通过以下方式定义两个表在查询中的关联方式：

• 指定每个表中要用于联接的列。典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。

• 指定用于比较各列的值的逻辑运算符（例如 = 或 <>）。

可以在 FROM 或 WHERE 子句中指定 内部联接；而只能在 FROM 子句中指定 外部联接。联接条件与 WHERE 和 HAVING 搜索条件相结合，用于控制从 FROM 子句所引用的基表中选定的行。

比如下列联接因为是内部联接，因此也可以改写为在WHERE条件中指定联接。

1      1
2    
3      
4 
5     
6     
7     -- FROM中指定联接（首选）
8    
9      
10 
11     
12     
13     
14    
15      
16 
17     
18     
19      2
20    
21      
22 
23     
24     
25     SELECT pv.ProductID, v.BusinessEntityID, v.Name
26    
27      
28 
29     
30     
31     
32    
33      
34 
35     
36     
37      3
38    
39      
40 
41     
42     
43     FROM Purchasing.ProductVendor 
44    
45      
46 
47     
48     
49     AS pv 
50    
51      
52 
53     
54     
55     
56    
57      
58 
59     
60     
61      4
62    
63      
64 
65     
66     
67     JOIN Purchasing.Vendor 
68    
69      
70 
71     
72     
73     AS v
74    
75      
76 
77     
78     
79     
80    
81      
82 
83     
84     
85      5    
86    
87      
88 
89     
90     
91     ON (pv.BusinessEntityID = v.BusinessEntityID)
92    
93      
94 
95     
96     
97     
98    
99      
100 
101     
102     
103      6
104    
105      
106 
107     
108     
109     WHERE StandardPrice > 
110    
111      
112 
113     
114     
115     10
116    
117      
118 
119     
120     
121     
122    
123      
124 
125     
126     
127      7    
128    
129      
130 
131     
132     
133     AND 
134    
135      
136 
137     
138     
139     Name 
140    
141      
142 
143     
144     
145     LIKE N
146    
147      
148 
149     
150     
151     'F%';
152    
153      
154 
155     
156     
157     
158    
159      
160 
161     
162     
163      8
164    
165      
166 
167     
168     
169     -- WHERE中指定联接
170    
171      
172 
173     
174     
175     
176    
177      
178 
179     
180     
181      9
182    
183      
184 
185     
186     
187     SELECT pv.ProductID, v.BusinessEntityID, v.Name
188    
189      
190 
191     
192     
193     
194    
195      
196 
197     
198     
199     10
200    
201      
202 
203     
204     
205     FROM Purchasing.ProductVendor 
206    
207      
208 
209     
210     
211     AS pv, Purchasing.Vendor 
212    
213      
214 
215     
216     
217     AS v
218    
219      
220 
221     
222     
223     
224    
225      
226 
227     
228     
229     11
230    
231      
232 
233     
234     
235     WHERE pv.VendorID = v.VendorID
236    
237      
238 
239     
240     
241     
242    
243      
244 
245     
246     
247     12    
248    
249      
250 
251     
252     
253     AND StandardPrice > 
254    
255      
256 
257     
258     
259     10
260    
261      
262 
263     
264     
265     
266    
267      
268 
269     
270     
271     13    
272    
273      
274 
275     
276     
277     AND 
278    
279      
280 
281     
282     
283     Name 
284    
285      
286 
287     
288     
289     LIKE N
290    
291      
292 
293     
294     
295     'F%';
296    
297      
298 
299     
300     
301     
302   
303     
304
305    
306    
307    

在 FROM 子句中指定联接条件有助于将这些联接条件与 WHERE 子句中可能指定的其他任何搜索条件分开，建议用这种方法来指定联接。简化的 ISO FROM 子句联接语法如下：

1     1FROM first_table  
2    
3      
4 
5     
6     
7     
8    
9      
10 
11     
12     
13     2join_type  
14    
15      
16 
17     
18     
19     
20    
21      
22 
23     
24     
25     3second_table 
26    
27      
28 
29     
30     
31     
32    
33      
34 
35     
36     
37     4[ON (join_condition)]
38    
39      
40 
41     
42     
43     
44   
45     
46
47    
48    
49    

join_type 指定要执行的联接类型

• 内部联接（典型的联接运算，使用类似于 = 或 <> 的比较运算符）。内部联接包括同等联接和自然联接。

• 外部联接。外部联接可以是左向外部联接、右向外部联接或完整外部联接。

  在 FROM 子句中可以用下列某一组关键字来指定外部联接：

• LEFT JOIN 或 LEFT OUTER JOIN。

  左向外部联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某一行在右表中没有匹配行，则在关联的结果集行中，来自右表的所有选择列表列均为空值。

• RIGHT JOIN 或 RIGHT OUTER JOIN

  右向外部联接是左向外部联接的反向联接。将返回右表的所有行。如果右表的某一行在左表中没有匹配行，则将为左表返回空值。

• FULL JOIN 或 FULL OUTER JOIN

  完整外部联接将返回左表和右表中的所有行。当某一行在另一个表中没有匹配行时，另一个表的选择列表列将包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

• 交叉联接

  交叉联接将返回左表中的所有行。左表中的每一行均与右表中的所有行组合。交叉联接也称作笛卡尔积。

join_condition 定义用于对每一对联接行进行求值的谓词（比较运算符或关系运算符）。

当 SQL Server 处理联接时，查询引擎会从 多种可行的方法中选择最有效的方法来处理联接。由于各种联接的实际执行过程会采用多种不同的优化，因此无法可靠地预测。

联接的例子可以参考 笔试题中的例子， SQL笔试50题（上） ， SQL笔试50题（下），在笔试题中有大量的内联接和左联接的例子。

4.10 UNION运算符

UNION 运算符可以将两个或多个 SELECT 语句的结果组合成一个结果集。

UNION 的结果集列名与 UNION 运算符中第一个 SELECT 语句的结果集中的列名相同。另一个 SELECT 语句的结果集列名将被忽略。

默认情况下，UNION 运算符将从结果集中删除重复的行。如果使用 ALL （即UNION ALL）关键字，那么结果中将包含所有行而不删除重复的行。

使用 UNION 运算符时需遵循下列准则：

• 在用 UNION 运算符组合的语句中，所有选择列表中的表达式（如列名称、算术表达式、聚合函数等）数目必须相同。

• 用 UNION 组合的结果集中的对应列或各个查询中所使用的任何部分列都必须具有相同的数据类型，并且可以在两种数据类型之间进行隐式数据转换，或者可以提供显式转换。例如，datetime 数据类型的列和 binary 数据类型的列之间的 UNION 运算符将不执行运算，直到进行了显式转换。但是，money 数据类型的列和 int 数据类型的列之间的 UNION 运算符将执行运算，因为它们可以进行隐式转换。

• 用 UNION 运算符组合的各语句中对应结果集列的顺序必须相同，因为 UNION 运算符按照各个查询中给定的顺序一对一地比较各列。

• 表中通过 UNION 运算所得到的列名称是从 UNION 语句中的第一个单独查询得到的。若要用新名称引用结果集中的某列（例如在 ORDER BY 子句中），必须按第一个 SELECT 语句中的方式引用该列

  11SELECT city AS Cities FROM stores_west2UNION 3SELECT city FROM stores_east4ORDER BY city
  2
  

4.11 EXCEPT和INTERSECT半联接

使用 EXCEPT 和 INTERSECT 运算符可以比较两个或更多 SELECT 语句的结果并返回 非重复值。

• EXCEPT 运算符返回由 EXCEPT 运算符左侧的查询返回、而又不包含在右侧查询所返回的值中的所有非重复值。（左边结果与 左右两边结果的交集的差集 A-A∩B）

• INTERSECT 返回由 INTERSECT 运算符左侧和右侧的查询都返回的所有非重复值。（两个查询结果的并集然后去重后的结果，A∪B）

使用 EXCEPT 或 INTERSECT 比较的结果集必须具有相同的结构。它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。

INTERSECT 运算符优先于 EXCEPT。

4.12 公用表表达式 WITH

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的 临时结果集。CTE 与派生表类似，具体表现在 不存储为对象，并且 只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还 可在同一查询中引用多次。

CTE 可用于：

• 创建递归查询。

• 在不需要常规使用视图时替换视图，也就是说，不必将定义存储在元数据中。

• 启用按从标量嵌套 select 语句派生的列进行分组，或者按不确定性函数或有外部访问的函数进行分组。

• 在同一语句中多次引用生成的表。

使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。

CTE 由表示 CTE 的表达式名称、可选列列表和定义 CTE 的查询组成。定义 CTE 后，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。CTE 也可用于 CREATE VIEW 语句，作为定义 SELECT 语句的一部分。

CTE 的基本语法结构如下：

1     1WITH expression_name [ ( column_name [,...n] ) ]
2    
3      
4 
5     
6     
7     
8    
9      
10 
11     
12     
13     2AS
14    
15      
16 
17     
18     
19     
20    
21      
22 
23     
24     
25     3( CTE_query_definition )
26    
27      
28 
29     
30     
31     
32    
33      
34 
35     
36     
37     4
38    
39      
40 
41     
42     
43     -- 运行 CTE 的语句
44    
45      
46 
47     
48     
49     
50    
51      
52 
53     
54     
55     5
56    
57      
58 
59     
60     
61     SELECT <column_list>
62    
63      
64 
65     
66     
67     
68    
69      
70 
71     
72     
73     6
74    
75      
76 
77     
78     
79     FROM expression_name;
80    
81      
82 
83     
84     
85     
86   
87     
88
89    
90    
91    
92
93
94    
95    
96    
97    
98      
99 
100     
101     
102      1
103    
104      
105 
106     
107     
108     -- 定义 CTE 查询别名和列名称
109    
110      
111 
112     
113     
114     
115    
116      
117 
118     
119     
120      2WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
121    
122      
123 
124     
125     
126     
127    
128      
129 
130     
131     
132      3AS
133    
134      
135 
136     
137     
138     
139    
140      
141 
142     
143     
144      4
145    
146      
147 
148     
149     
150     -- 定义CTE查询的结果集
151    
152      
153 
154     
155     
156     
157    
158      
159 
160     
161     
162      5(
163    
164      
165 
166     
167     
168     
169    
170      
171 
172     
173     
174      6    
175    
176      
177 
178     
179     
180     SELECT SalesPersonID, SalesOrderID, 
181    
182      
183 
184     
185     
186     YEAR(OrderDate) 
187    
188      
189 
190     
191     
192     AS SalesYear
193    
194      
195 
196     
197     
198     
199    
200      
201 
202     
203     
204      7    
205    
206      
207 
208     
209     
210     FROM Sales.SalesOrderHeader
211    
212      
213 
214     
215     
216     
217    
218      
219 
220     
221     
222      8    
223    
224      
225 
226     
227     
228     WHERE SalesPersonID 
229    
230      
231 
232     
233     
234     IS 
235    
236      
237 
238     
239     
240     NOT 
241    
242      
243 
244     
245     
246     NULL
247    
248      
249 
250     
251     
252     
253    
254      
255 
256     
257     
258      9)
259    
260      
261 
262     
263     
264     
265    
266      
267 
268     
269     
270     10
271    
272      
273 
274     
275     
276     -- 使用CTE查询的结果进行进一步的查询
277    
278      
279 
280     
281     
282     
283    
284      
285 
286     
287     
288     11
289    
290      
291 
292     
293     
294     SELECT SalesPersonID, 
295    
296      
297 
298     
299     
300     COUNT(SalesOrderID) 
301    
302      
303 
304     
305     
306     AS TotalSales, SalesYear
307    
308      
309 
310     
311     
312     
313    
314      
315 
316     
317     
318     12
319    
320      
321 
322     
323     
324     FROM Sales_CTE
325    
326      
327 
328     
329     
330     
331    
332      
333 
334     
335     
336     13
337    
338      
339 
340     
341     
342     GROUP 
343    
344      
345 
346     
347     
348     BY SalesYear, SalesPersonID
349    
350      
351 
352     
353     
354     
355    
356      
357 
358     
359     
360     14
361    
362      
363 
364     
365     
366     ORDER 
367    
368      
369 
370     
371     
372     BY SalesPersonID, SalesYear;
373   
374     
375
376    
377    
378    

本文项目地址：

https://github.com/firewang/sql50

(喜欢的话，Star一下)

阅读原文，或者访问该链接可以在线观看（该系列将更新至GitHub,并且托管到read the docs）

https://sql50.readthedocs.io/zh\_CN/latest/

参考网址：

• https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms175995(v=sql.105)

PS：

1. 后台回复“线性代数”，“SQL” 等任一关键词获取资源链接

2. 后台回复“联系“, “投稿“, “加入“ 等任一关键词联系我们

3. 后台回复 “红包” 领取红包

零维领域，由内而外深入机器学习

dive into machine learning

微信号：零维领域

英文ID：lingweilingyu

本文分享自微信公众号 - 零维领域（lingweilingyu）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。