CROSS JOIN

一，背景：网上找关于查看SQL执行计划的SQL时，发现这样一条SQL:

SELECT cp.usecounts as '使用次数'
            ,objtype as '类型'
            ,st.text            
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text not like '%sys%'

     这句SQL（’ CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st'）神奇了，竟然能够连接带参数的函数（参数不是固定的值，是另一张表的某个字段）。而正常我们如果像这样’ CROSS JOIN sys.dm_exec_sql_text(plan_handle) AS st’连接时，会得到下面的错误’ The multi-part identifier " plan_handle " could not be bound.’.。

二，OSS JOIN详解

Apply操作符的功能:
    可以让select查询语法与表值函数(TVF)进一步结合,在数据表含有XML字段时,结合XML的内容转换为新的数据表字段颇为方便.
                                                                                                                                        
Apply 工作原理:
   Apply操作符让符合查询的每一条记录都调用一次TVF函数,并将结果与原数据表的记录内容一起展开.
   Apply操作符定义在From子句内,使用方式与Join操作符类似. 其格式有两种类型:
   CROSS APPLY
   OUTER APPLY

       两者的差异只在当外部的数据表逐条将记录带入到TVF之后,若TVF并无符合的记录返回时, CROSS APPLY运算将不会显示该条记录,而OUTER APPLY依然会显示外部数据表对应的记录,但TVF相关的字段则以NULL显示.

三，示例

     初始化数据（下面仅仅是展示下CROSS APPLY的应用，例子举的差）：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dept]'))
DROP TABLE [dbo].[Dept]
GO
CREATE TABLE dbo.Dept(
    id INT,
    name VARCHAR(50)
)
GO

 
INSERT INTO dbo.Dept
SELECT 1,'软件工程学院'
UNION ALL
SELECT 2,'美术学院'
UNION ALL
SELECT 3,'外语学院'

SELECT * FROM dbo.Dept

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]'))
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE dbo.Employee(
    id INT,
    name VARCHAR(50),
    dept_id INT
)
GO
INSERT INTO dbo.Employee
SELECT 1,'Mike',1
UNION ALL
SELECT 2,'Ross',1
UNION ALL
SELECT 3,'Jion',2

SELECT * FROM dbo.Employee

    

    创建多函数，并使用CROSS JOIN 与OUTER JOIN:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEployeeCountByDeptID]'))
DROP FUNCTION [dbo].[GetEployeeCountByDeptID]
GO

CREATE FUNCTION dbo.GetEployeeCountByDeptID
(
   @dept_id INT
)
RETURNS TABLE
AS
RETURN
(
   SELECT E.dept_id, COUNT(*) AS employee_num FROM Employee AS E 
   WHERE E.dept_id=@dept_id
   GROUP BY E.dept_id
)

GO

 
SELECT *
FROM dbo.Dept AS D
 CROSS APPLY dbo.GetEployeeCountByDeptID(D.id)

SELECT *
FROM dbo.Dept AS D
 OUTER APPLY dbo.GetEployeeCountByDeptID(D.id)