• SQL Server 2005 中 Cross join & Cross Apply & Outer Apply 的区别


    SQL Server 2005 新增 cross apply 和 outer apply 联接语句,增加这两个东东有啥作用呢?

    我们知道有个 SQL Server 2000 中有个 cross join 是用于交叉联接的。实际上增加 cross apply 和 outer apply 是用于交叉联接表值函数(返回表结果集的函数)的, 更重要的是这个函数的参数是另一个表中的字段。这个解释可能有些含混不请,请看下面的例子:

    -- 1. cross join 联接两个表
    select *
      from TABLE_1 as T1
     cross join TABLE_2 as T2
    
    -- 2. cross join 联接表和表值函数,表值函数的参数是个“常量”
    select *
      from TABLE_1 T1
     cross join FN_TableValue(100)
    
    -- 3. cross join  联接表和表值函数,表值函数的参数是“表T1中的字段”
    select *
      from TABLE_1 T1
     cross join FN_TableValue(T1.column_a)
    
    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier "T1.column_a" could not be bound.
    

    最后的这个查询的语法有错误。在 cross join 时,表值函数的参数不能是表 T1 的字段, 为啥不能这样做呢?我猜可能微软当时没有加这个功能:),后来有客户抱怨后, 于是微软就增加了 cross apply 和 outer apply 来完善,请看 cross apply, outer apply 的例子:

    -- 4. cross apply
    select *
      from TABLE_1 T1
     cross apply FN_TableValue(T1.column_a)
    
    -- 5. outer apply
    select *
      from TABLE_1 T1
     outer apply FN_TableValue(T1.column_a)
    

    cross apply 和 outer apply 对于 T1 中的每一行都和派生表(表值函数根据T1当前行数据生成的动态结果集) 做了一个交叉联接。cross apply 和 outer apply 的区别在于: 如果根据 T1 的某行数据生成的派生表为空,cross apply 后的结果集 就不包含 T1 中的这行数据,而 outer apply 仍会包含这行数据,并且派生表的所有字段值都为 NULL

    下面的例子摘自微软 SQL Server 2005 联机帮助,它很清楚的展现了 cross apply 和 outer apply 的不同之处:

    -- cross apply
    select *
      from Departments as D
     cross apply fn_getsubtree(D.deptmgrid) as ST
    
    deptid      deptname      deptmgrid   empid       empname       mgrid       lvl
    ----------- -----------   ----------- ----------- -----------   ----------- ------
    1           HR            2           2           Andrew        1           0
    1           HR            2           5           Steven        2           1
    1           HR            2           6           Michael       2           1
    2           Marketing     7           7           Robert        3           0
    2           Marketing     7           11          David         7           1
    2           Marketing     7           12          Ron           7           1
    2           Marketing     7           13          Dan           7           1
    2           Marketing     7           14          James         11          2
    3           Finance       8           8           Laura         3           0
    4           R&D           9           9           Ann           3           0
    5           Training      4           4           Margaret      1           0
    5           Training      4           10          Ina           4           1
    
    (12 row(s) affected)
    
    -- outer apply
    select *
      from Departments as D
     outer apply fn_getsubtree(D.deptmgrid) as ST
    
    deptid      deptname      deptmgrid   empid       empname       mgrid       lvl
    ----------- -----------   ----------- ----------- -----------   ----------- ------
    1           HR            2           2           Andrew        1           0
    1           HR            2           5           Steven        2           1
    1           HR            2           6           Michael       2           1
    2           Marketing     7           7           Robert        3           0
    2           Marketing     7           11          David         7           1
    2           Marketing     7           12          Ron           7           1
    2           Marketing     7           13          Dan           7           1
    2           Marketing     7           14          James         11          2
    3           Finance       8           8           Laura         3           0
    4           R&D           9           9           Ann           3           0
    5           Training      4           4           Margaret      1           0
    5           Training      4           10          Ina           4           1
    6           Gardening     NULL        NULL        NULL          NULL        NULL
    
    (13 row(s) affected)
    

    注意 outer apply 结果集中多出的最后一行。 当 Departments 的最后一行在进行交叉联接时:deptmgrid 为 NULL,fn_getsubtree(D.deptmgrid) 生成的派生表中没有数据,但 outer apply 仍会包含这一行数据,这就是它和 cross join 的不同之处。

    下面是完整的测试代码,你可以在 SQL Server 2005 联机帮助上找到:

    -- create Employees table and insert values
    create table Employees
    (
      empid   int         not null,
      mgrid   int         NULL,
      empname varchar(25) not null,
      salary  money       not null
    )
    go
    
    
    -- create Departments table and insert values
    create table Departments
    (
      deptid    int not null primary key,
      deptname  varchar(25) not null
    )
    go
    
    -- fill datas
    insert into employees values(1 , NULL, 'Nancy'   , $10000.00)
    insert into employees values(2 , 1   , 'Andrew'  , $5000.00)
    insert into employees values(3 , 1   , 'Janet'   , $5000.00)
    insert into employees values(4 , 1   , 'Margaret', $5000.00)
    insert into employees values(5 , 2   , 'Steven'  , $2500.00)
    insert into employees values(6 , 2   , 'Michael' , $2500.00)
    insert into employees values(7 , 3   , 'Robert'  , $2500.00)
    insert into employees values(8 , 3   , 'Laura'   , $2500.00)
    insert into employees values(9 , 3   , 'Ann'     , $2500.00)
    insert into employees values(10, 4   , 'Ina'     , $2500.00)
    insert into employees values(11, 7   , 'David'   , $2000.00)
    insert into employees values(12, 7   , 'Ron'     , $2000.00)
    insert into employees values(13, 7   , 'Dan'     , $2000.00)
    insert into employees values(14, 11  , 'James'   , $1500.00)
    
    insert into departments values(1, 'HR',           2)
    insert into departments values(2, 'Marketing',    7)
    insert into departments values(3, 'Finance',      8)
    insert into departments values(4, 'R&D',          9)
    insert into departments values(5, 'Training',     4)
    insert into departments values(6, 'Gardening', NULL)
    go
    
    
    -- table-value function
    create function dbo.fn_getsubtree(@empid AS INT) returns @TREE table
    (
      empid   int not null,
      empname varchar(25) not null,
      mgrid   int null,
      lvl     int not null
    )
    as
    begin
      with Employees_Subtree(empid, empname, mgrid, lvl)
      as
      (
        -- Anchor Member (AM)
        select empid, empname, mgrid, 0
          from employees
         where empid = @empid
    
        union all
    
        -- Recursive Member (RM)
        select e.empid, e.empname, e.mgrid, es.lvl+1
          from employees as e
                  join employees_subtree as es
            on e.mgrid = es.empid
      )
    
    
      insert into @TREE
        select * from Employees_Subtree
    
      return
    end
    go
    
    -- cross apply query
    select *
      from Departments as D
     cross apply fn_getsubtree(D.deptmgrid) as ST
    
    
    -- outer apply query
    select *
      from Departments as D
     outer apply fn_getsubtree(D.deptmgrid) as ST

    本文由 www.sqlstudy.com 原创,版权所有,转载请注明作者和出处!

    本文链接:http://www.sqlstudy.com/sql_article.php?id=2008061302

  • 相关阅读:
    Unraid 8 虚拟机 KVM(Ubuntu Server、Windows 10)
    Unraid 9 优化 IPv6 访问(LANraragi 示例)
    1451. 重新排列句子中的单词
    144. 二叉树的前序遍历
    模拟键盘输入 keybd_event()
    Hive元数据信息对应MySQL数据库表
    书单
    Manjaro Gnome Hidpi 缩放问题
    Manjaro Linux 魔兽世界 使用黑盒工坊安装插件
    nginx系列【设置开机自启动】
  • 原文地址:https://www.cnblogs.com/tigris/p/2652863.html
Copyright © 2020-2023  润新知