You are here

OUTER APPLY Operator

APPLY operator

OUTER APPLY Operator

In this post we will discuss OUTER APPLY Operator in SQL Server 2016. The APPLY Operator combines the advantages of both joins and subqueries.

  • The OUTER apply extends the CROSS apply but it includes rows for those with NULL’s in columns for right table.
  • It behaves like a left outer join means it preserve the left side.
  • It also includes rows from the left side that get empty set from the right side, NULLs are used as placeholder.

Example:

Table: Department (Primary key=Id)

Iddepartname

1

IT

2

HR

3

Payroll

4

Admin

5

Sales

Table: Employee (Primary key=emp_id, Foreign key=did)

emp_id

Namesalary

Did

1

Riya50,000

1

2

Mary60,000

3

3

Sagar45,000

2

4

John56,000

1

5

Savita39,000

2

Select d. departname, E. name, E. salary
from Department as d
OUTER APPLY fn_getdata (d. id) as E;
Output:

Departname

name

Salary

IT

Riya

50,000

IT

John

56,000

HR

Sagar45,000

HR

Savita

39,000

Payroll

Mary60,000
AdminNULL

NULL

Sales

NULL

NULL



Leave a Reply

Top