SQL 交叉应用和外部应用基础
示例
在正确的表达式中使用表值函数时,将使用Apply。
创建一个Department表来保存有关部门的信息。然后创建一个Employee表,其中包含有关雇员的信息。请注意,每个员工都属于一个部门,因此Employee表与Department表具有参照完整性。
第一个查询从Department表中选择数据,并使用CROSSAPPLY评估Department表的每个记录的Employee表。第二个查询只是将Department表与Employee表连接在一起,并生成所有匹配的记录。
SELECT * FROM Department D CROSS APPLY ( SELECT * FROM Employee E WHEREE.DepartmentID= D.DepartmentID ) A GO SELECT * FROM Department D INNER JOIN Employee E OND.DepartmentID= E.DepartmentID
如果您查看它们产生的结果,则它是完全相同的结果集。它与JOIN有何不同以及如何帮助编写更有效的查询。
脚本2中的第一个查询从Department表中选择数据,并使用OUTERAPPLY为Department表的每个记录评估Employee表。对于那些在Employee表中不匹配的行,这些行包含NULL值,如在第5行和第6行的情况下所见。第二个查询仅在Department表和Employee表之间使用LEFTOUTERJOIN。如预期的那样,查询返回Department表中的所有行;即使对于那些在Employee表中不匹配的行。
SELECT * FROM Department D OUTER APPLY ( SELECT * FROM Employee E WHEREE.DepartmentID= D.DepartmentID ) A GO SELECT * FROM Department D LEFT OUTER JOIN Employee E OND.DepartmentID= E.DepartmentID GO
即使以上两个查询返回相同的信息,执行计划也会有所不同。但是在成本方面不会有太大的区别。
现在是时候查看一下真正需要APPLY运算符的位置了。在脚本#3中,我正在创建一个表值函数,该函数接受DepartmentID作为其参数,并返回属于该部门的所有员工。下一个查询从Department表中选择数据,并使用CROSSAPPLY与我们创建的函数联接。它从外部表表达式(在我们的示例中为Department表)传递每一行的DepartmentID,并为每一行评估函数,类似于相关的子查询。下一个查询使用OUTERAPPLY代替CROSSAPPLY,因此不同于CROSSAPPLY仅返回相关数据的情况,OUTERAPPLY也返回不相关的数据,将NULL放入丢失的列中。
CREATE FUNCTIONdbo.fn_GetAllEmployeeOfADepartment(@DeptID AS int) RETURNS TABLE AS RETURN ( SELECT * FROM Employee E WHEREE.DepartmentID= @DeptID ) GO SELECT * FROM Department D CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO SELECT * FROM Department D OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO
因此,现在如果您想知道,我们可以使用简单的联接代替上面的查询吗?那么答案是否定的,如果将上述查询中的CROSS/OUTERAPPLY替换为INNERJOIN/LEFTOUTERJOIN,指定ON子句(约为1=1)并运行查询,您将获得“多部分标识符”D.DepartmentID”无法绑定。”错误。这是因为使用JOIN时,外部查询的执行上下文不同于函数(或派生表)的执行上下文,并且您不能将外部查询的值/变量作为参数绑定到函数。因此,此类查询需要APPLY运算符。