In SQL Server database operations, a new application table operator is added to the version above 2005. The new apply table operator applies the right table expression to each row in the left table expression. It does not compute that table expression as a join, and the apply must first compute the left expression logically. The logical order of this calculation input allows the right expression to be associated with the left-table expression.
Apply has two forms, one is outer apply, one is cross apply, the difference is that specifying outer means that the result set will contain rows in the left table expression that make the right table expression empty, and specify cross, instead, The result set does not contain rows in the left table expression that makes the right table expression empty.
Understanding Cross APPLY
For example, a column in a largetable table stores data that is separated by a ":" Number, and when we do this, we may want to separate the value first, then separate each value in a single row, and then handle the table. This is only done in one line of processing, if we do this in the table, the number of rows in the multirow in a table, how to deal with it?
You can handle the above operation by using the Apply Table operator line statement.
Copy Code code as follows:
SELECT
A
From
Dbo. Largetable as LT--Actual table
CROSS APPLY
Dbo. Split (LT. Name, ': '--custom table-valued function, complete string-delimited
WHERE
A <> '--remove data from a field in the result table that is blank
-----------String Separator function-------------
CREATE Function [dbo]. [Split]
(
@Sql varchar (8000),
@Splits varchar (10)
)
Returns @temp Table (a varchar (100))
As
Begin
Declare @i Int
Set @Sql = RTrim (LTrim (@Sql))
Set @i = CharIndex (@Splits, @Sql)
While @i >= 1
Begin
Insert @temp Values (left (@Sql, @i-1))
Set @Sql = SubString (@Sql, @i+1,len (@Sql)-@i)
Set @i = CharIndex (@Splits, @Sql)
End
If @Sql <> '
Insert @temp Values (@Sql)
Return
End
------------Call the example-------------
SELECT
*
From
Dbo. Split (' 581::579::519::279::406::361::560 ', ': ')
The execution of apply: It logically evaluates the left table expression, and then applies the right expression to each row of the left-table expression. It is actually passing the column reference of the outer query as a parameter to the table-valued function.
We know that there is a cross join in the SQL Server2000 for cross joins. In fact, SQL Server 2005 adds cross apply and outer apply JOIN statements are used to cross join table-valued functions (functions that return a table result set), and more importantly, the parameter of this function is a field in another table.
This explanation may be somewhat ambiguous please see the following example:
Copy Code code as follows:
--1. Cross join joins two tables
SELECT *
From Table_1 as T1
Cross Join Table_2 as T2
--2. Cross join joins tables and table-valued functions, the parameter of the table-valued function is a "constant"
SELECT *
From Table_1 T1
Cross join Fn_tablevalue (100)
--3. Cross join joins tables 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, State 1, line 1
The multi-part identifier "t1.column_a" could not to be bound.
The last query has a syntax error. When cross join, the parameter of the table-valued function cannot be the field of the table T1, why can't you do it? I guess Microsoft didn't add this feature at the time: Later, after customers complained, Microsoft added cross apply and outer apply to improve.
Please see cross apply, outer apply example:
Copy Code code as follows:
--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 a cross join for each row in T1 with a derived table (a table-valued function based on the dynamic result set generated by the current row of data T1). The difference between cross apply and outer apply is that if a derived table generated from a row of T1 is empty, the result set cross apply does not contain this row of data in the T1, and outer apply will still contain the row of data, and all the field values of the derived table are Is NULL.
The following example is excerpted from the Microsoft SQL Server 2005 online Help, which clearly shows the difference between cross apply and outer apply:
Copy Code code as follows:
--Cross Apply query
SELECT *
From departments as D
CROSS APPLY Fn_getsubtree (d.deptmgrid) as ST
DeptID deptname deptmgrid empid empname mgrid lvl
----------- ----------- ----------- ----------- ----------- ----------- ------
1HR22Andrew1 0
1HR25Steven21
1HR26Michael21
2Marketing77Robert30
2Marketing711David7 1
2Marketing712Ron71
2Marketing713Dan7 1
2Marketing714James112
3Finance88Laura3 0
4R&D99Ann30
5 training 4 4 margaret 1 0
5 training 4 Ten ina 4 1
( row (s) affected) "
Copy Code code as follows:
--Outer Apply query
SELECT *
From departments as D
OUTER APPLY Fn_getsubtree (d.deptmgrid) as ST
DeptID deptname deptmgrid empid empname mgrid lvl
----------- ----------- ----------- ----------- ----------- ----------- ------
1HR22Andrew10
1HR25Steven21
1HR2 6Michael21
2Marketing77Robert30
2Marketing711David71
2Marketing712Ron71
2Marketing713Dan71
2Marketing714James112
3Finance88Laura30
4R&D99Ann30
5Training4 4Margaret10
5 training 4 ten ina 4 1
6 gardening NULL null null NULL NULL
( row (s) affected)
Note that the last line in the outer apply result set is more than one. When the last line of departments is in a cross join: There is no data in the derived table generated by Deptmgrid for Null,fn_getsubtree (d.deptmgrid), but outer apply will still contain this row of data, which is it and cross The difference between a join.
Here is the complete test code, which you can find on the SQL Server 2005 online Help:
Copy Code code as follows:
--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 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&d ', 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
