表连接函数Cross/Outer Apply

功能概述

Cross Apply 和 Outer Apply 均是表连接函数，可以用来连接两个表类型的表达式。常与表值函数（UnwindJson和 UnwindArray）一起使用。

Cross Apply

Cross Apply 与Join 类似，也是对两个表进行操作，子查询语句可以做为内部表。

Cross Apply 等效于隐式连接条件为1 = 1的 INNER JOIN（或更精确地说，它类似于具有相关子查询的 CROSS JOIN）。

我们来看这个SQL语句

select Table1.Name, tmp.Value as Tag from Table1 cross apply UnwindArray(SPLIT(Table1.Tags,',')) tmp

Table1.Tags是对Table1的列进行引用。

下图中可查看 CROSS APPLY 的工作过程（点击可查看大图）：

如果表值函数引用列，则必须将其与CROSS APPLY 或 OUTER APPLY一起使用，否则将无法正确解析数据。

Outer Apply

Outer Apply 与 Cross Apply 略有不同。

当与一个表值函数进行 Outer Apply 连接且表值函数返回空行集合时，实际上外部数据行在进行合并时，所有的单元格都是DBNull。所以合并后，都返回了DBNUll。

将上例中的Cross Apply 换成 Outer Apply 后，工作原理如下（点击可查看大图）：

可见，Outer Apply 与Join 类似，也是对两个表进行操作，子查询语句可以做为内部表。

Outer Apply 等效于隐式连接条件为1 = 1的 LEFT OUTER JOIN。

UnwindJson+Apply 示例

UnwindJson 有时会返回 JSON 文本作为单元格值。您可能很好奇，那我们如何使用这些 JSON 文本？答案是使用多个 UnwindJson 和 APPLY。

以下是一个包含公司部门，组和开发人员信息的 json 示例：

{
    "Company": {
        "Name": "GrapeCity",
        "Addr": "Xi'an",
        "Phone": "029-88331988",
        "Departments": [
            {
                "Id": "DD1",
                "Name": "Develop Department 1",
                "Groups": [
                    {
                        "Id": "DD1-GRP1",
                        "Name": "DD1 Group1",
                        "Developers": [
                            {
                                "Role": "PM",
                                "Name": "AAA"
                            },
                            {
                                "Role": "DL",
                                "Name": "BBB"
                            },
                            {
                                "Role": "Dev",
                                "Name": "CCC"
                            }
                        ]
                    },
                    {
                        "Id": "DD1-GRP2",
                        "Name": "DD1 Group2",
                        "Developers": [
                            {
                                "Role": "Dev",
                                "Name": "DDD"
                            },
                            {
                                "Role": "Tester",
                                "Name": "EEE"
                            }
                        ]
                    }
                ]
            },
            {
                "Id": "DD2",
                "Name": "Develop Department 2",
                "Groups": [
                    {
                        "Id": "DD2-GRP1",
                        "Name": "DD2 Group1",
                        "Developers": [
                            {
                                "Role": "PM",
                                "Name": "FFF"
                            },
                            {
                                "Role": "Dev",
                                "Name": "GGG"
                            }
                        ]
                    },
                    {
                        "Id": "DD2-GRP2",
                        "Name": "DD2 Group2",
                        "Developers": [
                            {
                                "Role": "Dev",
                                "Name": "HHH"
                            },
                            {
                                "Role": "Dev",
                                "Name": "III"
                            },
                            {
                                "Role": "Tester",
                                "Name": "JJJ"
                            }
                        ]
                    }
                ]
            },
            {
                "Id": "DD3",
                "Name": "",
                "Groups": [
                    {
                        "Id": "DD3-GRP1",
                        "Name": "DD3 Group1",
                        "Developers": [
                            {
                                "Role": "DL",
                                "Name": "KKK"
                            },
                            {
                                "Role": "Dev",
                                "Name": "LLL"
                            },
                            {
                                "Role": "Tester",
                                "Name": "MMM"
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

现在，我们使用SQL 语句展开其所有嵌套的数组并生成概览数据表。

select
    departmentDetails.Id as DepId,
    groupDetails.Id as GroupId,
    developers.Name as DevName,
    developers.Role as DevRole
from
    UnwindJson(@source, '$.Company.Departments[*]') departments
outer apply
    UnwindJson(departments.Value) departmentDetails with ([Id] varchar, [Groups] varchar AS JSON)
outer apply
    UnwindJson(departmentDetails.Groups, '$[*]') groupDetails with ([Id] varchar, [Developers] varchar AS JSON)
outer apply
    UnwindJson(groupDetails.Developers, '$[*]') developers with ([Name] varchar, [Role] varchar)

展开后，结果如下：

DepId	GroupId	DevName	DevRole
DD1	DD1-GRP1	AAA	PM
DD1	DD1-GRP1	BBB	DL
DD1	DD1-GRP1	CCC	Dev
DD1	DD1-GRP2	DDD	Dev
DD1	DD1-GRP2	EEE	Tester
DD2	DD2-GRP1	FFF	PM
DD2	DD2-GRP1	GGG	Dev
DD2	DD2-GRP2	HHH	Dev
DD2	DD2-GRP2	III	Dev
DD2	DD2-GRP2	JJJ	Tester
DD3	DD3-GRP1	KKK	DL
DD3	DD3-GRP1	LLL	Dev
DD2	DD3-GRP1	MMM	Tester