Cross apply and OUTER apply Difference

SQL Server 2005 adds cross apply and outer apply JOIN statements to add these two things to what effect?

We know that there is a cross join in SQL Server 2000 that is used for interleaving. In fact, adding cross apply and outer apply is used to intersect table-valued functions (functions that return table result sets), and more importantly, the parameters of this function are fields 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 T1
Cross Join Table_2 as T2
--2. Cross join joins table and table-valued functions, the parameters of a table-valued function are "constants"
SELECT *
From Table_1 T1
Cross 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 T1
Cross join Fn_tablevalue (t1.column_a)

MSG 4104, Level A, State 1, line 1
The multi-part identifier "t1.column_a" could not be 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 crossapply and outer apply to improve, see cross apply, outer apply example:

--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 and outer apply 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 a derived table that is generated from a row of T1 is empty, the result set of cross apply does not contain this row of data in T1, and outer apply still contains this row of data, and all the field values of the derived table Are NULL.

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

--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 David 7 1
2 Marketing 7 Ron 7 1
2 Marketing 7 Dan 7 1
2 Marketing 7 James 11 2
3 Finance 8 8 Laura 3 0
4 R/R 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 Ina 4 1

(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 David 7 1
2 Marketing 7 Ron 7 1
2 Marketing 7 Dan 7 1
2 Marketing 7 James 11 2
3 Finance 8 8 Laura 3 0
4 R/R 9 9 Ann 3 0
5 Training 4 4 Margaret 1 0
5 Training 4 Ina 4 1
6 gardening null NULL NULL NULL

(s) affected)
Note outer the last row in the apply result set. When the last line of departments is cross-joined: Deptmgrid does not have data in the derived table generated for Null,fn_getsubtree (d.deptmgrid), but outer apply still contains this row of data, which is The difference between joins.

The following is the complete test code that you can find on the SQL Server 2005 online Help:

--Create Employees table and insert values
IF object_id (' Employees ') is not NULL
DROP TABLE Employees
GO
CREATE TABLE Employees
(
Empid INT not NULL,
Mgrid INT NULL,
EmpName VARCHAR (+) not NULL,
Salary money is not NULL
)
GO
IF object_id (' departments ') is not NULL
DROP TABLE Departments
GO
--Create Departments table and insert values
CREATE TABLE Departments
(
DeptID INT not NULL PRIMARY KEY,
Deptname VARCHAR (+) 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, ' Janet ', 00.00)
INSERT into Employees VALUES (4,1, ' Margaret ', 00.00)
INSERT into Employees VALUES (5,2, ' Steven ', 00.00)
INSERT into Employees VALUES (6,2, ' Michael ', 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)
INSERT into Employees VALUES (13,7, ' Dan ', 00.00)
INSERT into Employees VALUES (14,11, ' James ', 00.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/R ', 9)
INSERT into Departments VALUES (5, ' Training ', 4)
INSERT into Departments VALUES (6, ' gardening ', NULL)
GO
--select * FROM Departments

--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 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

Cross apply and OUTER apply Difference