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
No responses yet