Sql Server - Apply Operator Part- 2

Apply Operator 

First read (Sql Server - Apply Operator Part- 1)
 http://sunitkanyan.blogspot.in/2015/01/sql-server-apply-operator-part-1.html 

APPLY operator allow you to call user defined tabular function with outer table(s).And you can pass outer table column as argument to function.  

  Type :-

•     CROSS APPLY (will not return the outer tables row if function table has no row corresponding to it.)
•     OUTER APPLY (returns NULL values instead of function columns.)

Example Demonstrating both types :-

Script to create table and tabular function >>

--Table1

Create Table Table1
(
id int identity(1,1),
name nvarchar(255) not null,
empcode nvarchar(255) primary key,
)
go
--Table2

Create Table Table2
(
id int identity(1,1),
empcode nvarchar(255) ,
salary float,

)
go
--tabular function , it will returning table2's records basis on empcode

--tabular function
CREATE FUNCTION fncGetInfo (@empcode nvarchar(255))
RETURNS TABLE
AS
RETURN
(
--Outer Query
SELECT *
FROM Table2
WHERE empcode=@empcode
)
go
--insert data in table 1 & table 2

--Cross Apply
SELECT table1.*,t2.salary FROM table1
Cross APPLY
dbo.fncGetInfo(table1.empcode) as t2

--Outer Apply
SELECT table1.*,t2.salary FROM table1
Outer APPLY
dbo.fncGetInfo(table1.empcode) as t2

images :-