Cross Apply and outer apply

Use the Apply operator to implement each call table-valued function returned by an external table expression for a query operation. The table-valued function acts as the right input and the outer table expression as the left input.

The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is left input

The list of columns returned in the column set, followed by the right input.

There are two forms of apply: cross apply and OUTER apply.

Cross APPLY returns only the rows in the external table that generated the result set through the table-valued function.

OUTER APPLY returns both the row that generated the result set and the row that does not produce the result set, where the value in the column generated by the table-valued function is null.



CREATE TABLE Employee
(
emp_id int NOT NULL,
mgr_id int NULL,
Emp_name varchar () NOT NULL,
Emp_salary money is not NULL,
Constraint pk_id primary KEY (emp_id)
)

INSERT into employee Select 1,null, ' Forget ', 4500
UNION ALL
Select 2, 1, ' Find ', 2500
UNION ALL
Select 3, 2, ' You will ', 3500
UNION ALL
Select 4, 3, ' Beef cattle ', 1500
UNION ALL
Select 5, 4, ' get ', 500
UNION ALL
Select 6, 5, ' love color put ', 300
UNION ALL
Select 7, 6, ' love each other ', 1000
UNION ALL
Select 8, 4, ' Assad ', 300
UNION ALL
Select 9, 8, ' Aston ', 1000

CREATE TABLE Departments
(
dep_id int Identity (primary) key,
Dep_name varchar (+) NOT NULL,
dep_m_id int NULL references employee (EMP_ID)
)
Insert Departments select ' Generation department ', 2
UNION ALL
Select ' Sales department ', 7
UNION ALL
Select ' Machining department ', 8
UNION ALL
Select ' Inventory department ', 9
UNION ALL
Select ' Management department ', 4
UNION ALL
Select ' Security department ', null

Create function Gtree
(
@emp_id int
)
Returns @tree table
(
emp_id int NOT NULL,
Emp_name varchar () NOT NULL,
mgr_id int NULL,
LVL int NOT NULL
)
As
Begin
With Emp_subtree (EMP_ID,EMP_NAME,MGR_ID,LVL)
As
(
Select emp_id,emp_name,mgr_id,0 from employee where [email protected]_id
UNION ALL
Select E.emp_id,e.emp_name,e.mgr_id,es.lvl+1
From employee e join Emp_subtree es on e.emp_id=es.emp_id
)
Insert @tree SELECT * FROM Emp_subtree
Return
End
SELECT * FROM Employee

SELECT * FROM departments as a
Cross Apply
Gtree (a.dep_m_id) as B

SELECT * FROM departments as a
Outer Apply
Gtree (a.dep_m_id) as B

Cross Apply and outer apply