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)

Id departname

1

IT

2

HR

3

Payroll

4

Admin

5

Sales

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

emp_id

Name salary

Did

1

Riya 50,000

1

2

Mary 60,000

3

3

Sagar 45,000

2

4

John 56,000

1

5

Savita 39,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

Sagar 45,000

HR

Savita

39,000

Payroll

Mary 60,000
Admin NULL

NULL

Sales

NULL

NULL



Leave a Reply

Top