SQL: APPLY Operator
In this post we will discuss APPLY operator in SQL Server 2016. APPLY operator is powerful operator that applies the right table expression to each row from the left table.
The Two forms of the APPLY operator are:
i) CROSS APPLY
ii) OUTER APPLY
The APPLY operator evaluates the left side, and for each of the left rows, applies the table expression provided in the right side. The query in the right side can have references to elements from the left side.
CROSS APPLY Operator
- The CROSS APPLY operator operates on right to left.
- The right table expression has a reference from the left table.
- The right expression is applied to each row from the left input.
- CROSS APPLY is different from OUTER APPLY, if right expression returns empty row for left row, the left row is not return, operator behaves like a cross join between the right result and that row.
- It returns only match rows.
- It behaves like an inner join.
Table: Department (Primary key =Id)
Table: Employee (Primary key= emp_id, Foreign key= did)
- Table value function:
Create function fn_getdata (@id int)
Select * from employee where did=@id;
- Select d. departname, E. name, E. salary
from Department as d
CROSS APPLY fn_getdata (d. id) as E;