在使用sql进行多表查询时,我们经常使用到左连接(left join)来对左表进行扩展,此时,输入的左表和右表通过在两边指定字段进行匹配,如果指定的两个字段相等,则返回结果。逻辑上,我们可以理解为对左表进行一次基于行的遍历,为每一行在右表中找出匹配的所有行,返回结果集,最后将由左表每一行产生的结果集合并为最终的结果集;而apply的就好像是左连接的升级版本,只是这里的右表是函数或表达式,将左表中的每一行的某一字段或多个字段输入到右表进行一次运算,最后将所有结果集合并为最终结果集。
Apply又分为cross apply和outer apply两种,上面介绍了cross apply,outer apply相较于cross apply区别是:当右表返回的结果集是空集,cross apply不会返回左表的数据,而outer apply会返回左表的数据并将右表补为NULL。
下面给出示例:
现有两张表分别为种类表和产品表:
Categories
categroyid | categoryname |
---|---|
1 | Beverages |
2 | Condiments |
3 | Confections |
Products
productid | productname | categroyid |
---|---|---|
1 | Product HHYDP | 1 |
2 | Product RECZE | 1 |
24 | Product QOGNU | 1 |
3 | Product IMEHJ | 2 |
4 | Product KSBRM | 2 |
5 | Product EPEIM | 2 |
16 | Product PAFRH | 3 |
19 | Product XKXDO | 3 |
20 | Product QHFFP | 3 |
首先根据种类表找出所有属于种类表列出种类的产品,此时使用左连接和cross apply都可以实现。
/*LEFT JOIN*/
SELECT
C.categoryid,
C.categoryname,
P.productid,
P.productname
FROM
[Production].[Categories] AS C
LEFT JOIN Production.Products P ON C.categoryid = P.categoryid
/*CROSS APPLY*/
SELECT
C.categoryid,
C.categoryname,
P.productid,
P.productname
FROM
[Production].[Categories] AS C CROSS APPLY (
SELECT
productname,
productid
FROM
Production.Products
WHERE
categoryid = C.categoryid
) AS P
会发现两种写法得到的结果相同:
categoryid | categoryname | productid | productname |
---|---|---|---|
1 | Beverages | 1 | Product HHYDP |
1 | Beverages | 2 | Product RECZE |
1 | Beverages | 24 | Product QOGNU |
2 | Condiments | 3 | Product IMEHJ |
2 | Condiments | 4 | Product KSBRM |
2 | Condiments | 5 | Product EPEIM |
3 | Confections | 16 | Product PAFRH |
3 | Confections | 19 | Product XKXDO |
3 | Confections | 20 | Product QHFFP |
此时,如果在种类表中加一个种类,但是在产品表中却找不到该种类的产品,左连接会返回左表,为右表补NULL,而cross apply不会。此时就需要将cross apply改为outer apply以达到和左连接相同的结果:
Categories
categroyid | categoryname |
---|---|
1 | Beverages |
2 | Condiments |
3 | Confections |
4 | Dairy Products |
Result
categoryid | categoryname | productid | productname |
---|---|---|---|
1 | Beverages | 1 | Product HHYDP |
1 | Beverages | 2 | Product RECZE |
1 | Beverages | 24 | Product QOGNU |
2 | Condiments | 3 | Product IMEHJ |
2 | Condiments | 4 | Product KSBRM |
2 | Condiments | 5 | Product EPEIM |
3 | Confections | 16 | Product PAFRH |
3 | Confections | 19 | Product XKXDO |
3 | Confections | 20 | Product QHFFP |
4 | Dairy Products |