CROSS APPLY & OUTER APPLY IN SQL SERVER

Cross Apply & Outer Apply In SQL Server

• SQL Server 2005 introduced the APPLY operator.
• There are 2 forms of APPLY operator.
  • Cross Apply
  • Outer Apply

CROSS APPLY & OUTER APPLY

• In SQL, APPLY operator is like a join clause and it allows joining between two table expressions i.e. joining a left table expression with a right table expression.
• But if you want to use Cross or Outer Apply then right table expression should be table valued function.
• In SQL, It is used to join a table with a table-valued function.
• In SQL, The difference between the join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.
• In SQL, The APPLY operator allows you to join two table expressions, the right table expression is processed every time for each row from the left table expression.
• The left table expression is evaluated first and then the right table expression is evaluated against each row of the left table expression for the final result set.
• In SQL, Cross Apply is just like Inner Join.
• In SQL, Outer Apply is just like Left Join.
• In SQL, Cross Apply returns only matching rows.
• In SQL, Outer Apply returns both matching and non-matching rows, the unmatched columns of the table valued function will be set to NULL.
• In SQL, If you want to use joins then you have to use tables as a result-set on both left or right but if you want to use table valued function as a result-set then you have to use Cross Apply or Outer Apply.

SQL Query For Cross Apply & Outer Apply

create table teachers
(
teacher_id int identity primary key,
teacher_name varchar(50),
teacher_gender varchar(50),
teacher_qual varchar(50),
teacher_sal int
);

insert into teachers values
('Asad','Male','BSIT',28000),
('Khalid','Male','MBA',29000),
('Amjad','Male','MPHIL',30000),
('Adeel','Male','BSIT',27000),
('Sahar','Female','BSC',25000),
('Farukh','Male','BBA',26000);

select * from teachers;

create table students
(
std_id int primary key identity,
std_name varchar(50),
std_gender varchar(50),
std_age int,
std_class int,
t_id int foreign key references teachers(teacher_id)
);

insert into students values
('Anas','Male',22,12,2),
('Anum','Female',21,11,3),
('Zain','Male',23,12,1),
('Furqan','Male',21,10,2),
('Saba','Female',21,11,4),
('Amna','Female',23,12,3);

select * from teachers;
select * from students;

select * from students
inner join teachers
on teachers.teacher_id = students.t_id;

select s.std_name,s.std_class, t.teacher_name,t.teacher_qual
from students as s
inner join teachers as t
on t.teacher_id = s.t_id;

select s.std_name,s.std_class, t.teacher_name,t.teacher_qual
from teachers as t
left join students as s
on t.teacher_id = s.t_id;


select s.std_name,s.std_class, t.teacher_name,t.teacher_qual
from teachers as t
inner join fn_GetStudentsByTeacherId(t.teacher_id) as s
on t.teacher_id = s.t_id;

select s.std_name,s.std_class, t.teacher_name,t.teacher_qual
from teachers as t
cross apply fn_GetStudentsByTeacherId(t.teacher_id) as s
--on t.teacher_id = s.t_id;

select t.teacher_name,t.teacher_qual,s.std_name,s.std_class
from teachers as t
outer apply fn_GetStudentsByTeacherId(t.teacher_id) as s

create function fn_GetStudentsByTeacherId(@teacher_id int)
returns table
as
Return
(
	select * from students 
	where t_id = @teacher_id
)

select * from fn_GetStudentsByTeacherId(2);

Download Notes Of This Blog, From The Link Given Below.

https://www.mediafire.com/file/4a6eu67l4agl36a/CROSS+APPLY+&+OUTER+APPLY+IN+SQL+SERVER.pptx/file