Use of T-SQL apply

SQL Server 2005 New Cross apply and outer Apply Join statement, add these two things have what role?

We know that there is a cross join in SQL Server . In fact, adding cross apply and outer Apply is used to intersect table-valued functions (functions that return table result sets), More importantly, the parameter of this function is a field in another table. This explanation may be somewhat ambiguous, please see the following example:

-- 1. Cross join joins two tables

SELECT * from  table_1 as T1cross join table_2 as T2

"This sentence is actually seeking Cartesian product."
-- 2. Cross join joins table and table-valued functions, the parameters of table-valued functions are "constants"

SELECT * FROM  table_1 T1cross join Fn_tablevalue (100)

-- 3. Cross join joins table and table-valued functions, the parameters of table-valued functions are "fields in table T1"

SELECT * FROM  table_1 T1cross join Fn_tablevalue (t1.column_a)

Error:

MSG 4104, Level A, State 1, line 1The multi-part identifier "T1.column_a" could is not bound.

The syntax of the last query is wrong. In cross join , the parameters of a table-valued function cannot be a field of table T1, so why can't you do that? I guess Microsoft did not add this feature:), and then after the customer complained, so Microsoft added cross apply and outer apply to improve, see Cross apply, outer Apply Example:

-- 4. Cross Apply

SELECT * FROM  table_1 T1cross apply Fn_tablevalue (t1.column_a)

-- 5. Outer Apply

SELECT * FROM  table_1 t1outer apply Fn_tablevalue (t1.column_a)

Cross Apply and outer apply makes a cross join for each row in T1 and the derived table (the table-valued function generates a dynamic result set based on the T1 current row data).  The difference between cross apply and outer Apply is that if the derived table generated from a row of T1 is empty, the Cross The result set after apply does not contain this row of data in T1, and outer apply still contains this row of data, and all field values of the derived table are NULL.

The following example is excerpted from the Microsoft SQL Server 2005 online Help, which clearly shows cross apply and outer The difference between apply:

-- Cross Apply

SELECT * FROM  departments as Dcross apply Fn_getsubtree (D.deptmgrid) as ST

DeptID deptname deptmgrid empid empname mgrid lvl--------------------------------------           ----------------------------------1 HR 2 2 Andrew 1 01 HR 2 5 Steven 2 hr 2 6 M Ichael 2 Marketing 7 7 Robert 3 Marketi           Ng 7 David 7 Marketing 7 Ron           7 Marketing 7 Dan 7 Marketing 7 James Finance 8 8 Laura 3 0           4 R/R 9 9 Ann 3 Training 4 4 Margaret 1           Training 4 Ina 4 1 (s) affected 

-- Outer Apply

SELECT * FROM  departments as Douter apply Fn_getsubtree (D.deptmgrid) as ST

DeptID deptname deptmgrid empid empname mgrid lvl--------------------------------------           ----------------------------------1 HR 2 2 Andrew 1 01 HR 2 5 Steven 2 hr 2 6 M Ichael 2 Marketing 7 7 Robert 3 Marketi           Ng 7 David 7 Marketing 7 Ron           7 Marketing 7 Dan 7 Marketing 7 James Finance 8 8 Laura 3 0           4 R/R 9 9 Ann 3 Training 4 4 Margaret 1           Training 4 Ina 4 gardening NULL NULL NULL NULL NULL (s) affected)

Note   outer  apply the last row in the result set. When the last line of departments is cross-joined: Deptmgrid is  , NULL , Fn_getsubtree (D.deptmgrid) does not have data in the derived table generated , but   outer  apply will still contain this line of data, which is it and   cross   The difference between join  . &NBSP

Below is the complete test code, which you can find on SQL server  2005   online Help:  

--Create Employees table and insert Valuesif object_id (' Employees ') is not nulldrop table Employeesgocreate table Employe ES (empid int not null,mgrid int null,empname VARCHAR (+) not null,salary money not NULL) goif object_id (' departments ') is N OT Nulldrop table departmentsgo--Create departments table and insert Valuescreate table departments (DeptID INT not NULL P Rimary key,deptname VARCHAR (+ null,deptmgrid INT) go--fill datasinsert into employees VALUES (1,null, ' Nancy ', 00.00 INSERT into employees values (2,1, ' Andrew ', 00.00) insert to employees values (3,1, ' Janet ', 00.00) insert INTO Employe Es values (4,1, ' Margaret ', 00.00) insert into employees values (5,2, ' Steven ', 00.00) insert into employees values (6,2, ' M  Ichael ', 00.00) insert into employees values (7,3, ' Robert ', 00.00) insert into employees values (8,3, ' Laura ', 00.00) insert Into employees values (9,3, ' Ann ', 00.00) insert into employees values (10,4, ' Ina ', 00.00) insert into employees values ( 11,7, ' David ', 00.00) inserT into employees values (12,7, ' Ron ', 00.00) inserts into employees values (13,7, ' Dan ', 00.00) inserts into employees values (14,11, ' James ', 00.00) INSERT into departments values (1, ' HR ', 2) insert to departments values (2, ' Marketing ', 7) insert into departments VALU   ES (3, ' Finance ', 8) INSERT into departments values (4, ' R/R ', 9) INSERT into departments values (5, ' Training ', 4) insert Into Departments VALUES (6, ' gardening ', NULL) go--select * from departments--table-value functionif object_id (' Fn_getsub   Tree ') is not Nulldrop function Fn_getsubtreegocreate 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 in e.mgrid = es.empid) SELECT * from Em PLoyees_subtree) go--Cross apply Queryselect *from departments as D cross apply Fn_getsubtree (D.deptmgrid) as st--Oute R Apply Queryselect *from departments as D OUTER apply Fn_getsubtree (D.deptmgrid) as ST-------------------Two small examples of Chinese wind finishing- ------------------------------CREATE TABLE #T (name varchar) INSERT into #T values (' Zhang San ') insert into #T values (' John Doe ')  INSERT into #T values (NULL) CREATE TABLE #T2 (name varchar (10), course varchar (10), fractional int) insert into #T2 values (' Zhang San ', ' language ', () insert into #T2 values (' Zhang San ', ' math ', Zhang San) insert into #T2 values (' ', ' physical ', ') insert into #T2 values (NULL, ' Math ', 5         0)--drop table #t, #T2goselect * from #T across apply (select course, score from #t2 where name =a. Name) b/* Name Course Score-------------------------------Zhang San Chinese 74 Three mathematics 83 sheets three physical 93 (3 rows affected) */ SELECT * FROM #T aouter apply (select course, score from #t2 where name =a. Name) b/* Name Course score--------------- ----------------Zhang San languages        74 Three Mathematics 83 sheets three physics 93 john Doe NULL NULLNULL NULL NULL (5 rows affected) */ 

Use of T-SQL apply