[Reprinted] Cross apply & outer apply

Today, I found a useless SQL statement at work, outer apply, so I searched for it.

Reprinted from: http://blog.csdn.net/htl258/archive/2009/09/10/4537421.aspx

In SQL Server 2005, the cross apply and outer apply join statements are added. What is the role of adding these two statements?

We know that one SQL Server 2000 has a cross join used for cross join. In fact, adding cross apply and outer apply is used to cross join the table value function (the function that returns the table result set). More importantly, the parameter of this function is a field in another table. This explanation may be ambiguous. Please refer to the following example:

-- 1. Cross join two tables
Select *
From table_1 as T1
Cross join table_2 as T2
-- 2. Cross join joins tables and Table value functions. The parameters of the Table value function are constants"
Select *
From table_1 T1
Cross join fn_tablevalue (100)
-- 3. Cross join joins tables and Table value functions. The parameter of the Table value function is "field in Table T1"
Select *
From table_1 T1
Cross join fn_tablevalue (t1.column _)

MSG 4104, level 16, state 1, line 1
The multi-part identifier "t1.column _ A" cocould not be bound.
The last query syntax is incorrect. During cross join, the table value function parameter cannot be a field of table T1. Why cannot this problem be solved? I guess Microsoft didn't add this feature at the time :). After some customers complained about it, Microsoft added cross apply and outer apply to improve it. For details, see cross apply and outer apply:

-- 4. Cross apply
Select *
From table_1 T1
Cross apply fn_tablevalue (t1.column _)

-- 5. Outer apply
Select *
From table_1 T1
Outer apply fn_tablevalue (t1.column _)
Cross apply and outer apply perform a cross join between each row in T1 and the derived table (the table value function generates a dynamic result set based on the data of the current row in T1. The difference between cross apply and outer apply is that if the derived table generated based on a row of T1 is empty, the result set after cross apply does not contain this row of data in T1, however, outer apply still contains this row of data, and all fields in the derived table are null.

The following example is taken from Microsoft SQL Server 2005 online help. It clearly shows the differences between cross apply and outer apply:

-- Cross apply
Select *
From orders as d
Cross apply fn_getsubtree (D. deptmgrid) as St
Deptid deptname deptmgrid empid empname mgrid LVL
------------------------------------------------------------------------
1 hr 2 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 orders as d
Outer apply fn_getsubtree (D. deptmgrid) as St
Deptid deptname deptmgrid empid empname mgrid LVL
------------------------------------------------------------------------
1 hr 2 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

(13 row (s) affected)
Note the last row in the outer apply result set. When the last row of the departments performs a cross join: deptmgrid is null, fn_getsubtree (D. the derived table generated by deptmgrid does not have data, but outer apply still contains this row of data, which is different from cross join.

The following is a complete testCodeYou can find the following on SQL Server 2005 online help:

-- create employees table and insert values
If object_id ('ployees ') is not null
drop table employees
go
Create Table employees
(
empid int not null,
mgrid int null,
empname varchar (25) not null,
salary money not null
)
go
If object_id ('ances ') is not null
drop table partition ments
go
-- create partition ments table and insert values
Create Table partition ments
(
deptid int not null primary Key,
deptname varchar (25) not null,
deptmgrid int
)
go

-- fill datas
insert into employees values (1, null, 'nancy ', 00.00)
insert into employees values (2, 1, 'Andrew', 00.00)
insert into employees values (3, 1, 'cid', 00.00)
insert into employees values (4, 1, 'Margaret, 00.00)
insert into employees values (00.00, 'steven ', 00.00)
insert into employees values (, 'Michael)
insert into employees values (00.00, 'Robert ', 00.00)
insert into employees values (, 'Laura)
insert into employees values (00.00, 'ann ', 00.00)
insert into employees values (, 'ina)
insert into employees values (00.00, 'David', 00.00)
insert into employees values (, 'ron)
insert into employees values (00.00, 'dan', 00.00)
insert into employees values (, 'James ',)

insert into values ments values (1, 'hr', 2)
insert into values ments values (2, 'marketing', 7)
insert into values ments values (3, 'Finance ', 8)
insert into values ments values (4, 'r & D', 9)
insert into attributes values (5, 'Training', 4)
insert into attributes values (6, 'gardening', null)
go
-- select * from orders

-- table-value function
If object_id ('fn _ getsubtree ') is not null
drop function fn_getsubtree
go
Create Function DBO. fn_getsubtree (@ empid as INT)
Returns table
as
return (
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
)
select * From employees_subtree
)
go

-- Cross apply Query
Select *
From orders as d
Cross apply fn_getsubtree (D. deptmgrid) as St

 

-- Outer apply Query
Select *
From orders as d
Outer apply fn_getsubtree (D. deptmgrid) as St

 

----------------- Two small examples of Chinese style arrangement -------------------------------
Create Table # T (name varchar (10 ))
Insert into # T values ('zhang san ')
Insert into # T values ('Li si ')
Insert into # T values (null)

Create Table # T2 (name varchar (10), course varchar (10), score INT)
Insert into # T2 values ('zhang san', 'China', 74)
Insert into # T2 values ('zhang san', 'mat', 83)
Insert into # T2 values ('zhang san', 'Physical ', 93)
Insert into # T2 values (null, 'mat', 50)

-- Drop table # T, # T2
Go

Select
*
From
# T
Cross apply
(Select course, score from # T2 where name = A. Name) B

/*
Name course score
-------------------------------
Zhang San Language 74
James math 83
Zhang San physical 93

(3 rows affected)

*/

Select
*
From
# T
Outer apply
(Select course, score from # T2 where name = A. Name) B
/*
Name course score
-------------------------------
Zhang San Language 74
James math 83
Zhang San physical 93
Li Si null
Null null

(5 rows affected)

*/

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/09/10/4537421.aspx