You are here

SQL: APPLY Operator

APPLY operator

SQL: APPLY Operator

In this post we will discuss APPLY operator in SQL Server 2016. APPLY operator is powerful operator that applies the right table expression to each row from the left table.

The Two forms of the APPLY operator are:

i) CROSS APPLY

ii) OUTER APPLY

The APPLY operator evaluates the left side, and for each of the left rows, applies the table expression provided in the right side. The query in the right side can have references to elements from the left side.

CROSS APPLY Operator

  • The CROSS APPLY operator operates on right to left.
  • The right table expression has a reference from the left table.
  • The right expression is applied to each row from the left input.
  • CROSS APPLY is different from OUTER APPLY, if right expression returns empty row for left row, the left row is not return, operator behaves like a cross join between the right result and that row.
  • It returns only match rows.
  • It behaves like an inner join.

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

Marry 60,000 3
3 Sagar 45,000

2

4

John 56,000 1
5 Savita 39,000

2

  • Table value function:

Create function fn_getdata (@id int)

Returns table

As

Return

(

Select * from employee where did=@id;

)

  • Select d. departname, E. name, E. salary

from Department as d

CROSS APPLY fn_getdata (d. id) as E;

Output:

Departname

Name salary
HR Sagar

45,000

HR

Savita 39,000
IT Riya

50,000

IT

John 56,000
Payroll Marry

60,000

You can also by SQL 70-761 book write by me fromĀ Amazon.
You can also by Kotlin Book write by me fromĀ Amazon.

Microsoft SQL 70-762 Practice Test.

Microsoft MCSA 70-761 Practice Test:

One thought on “SQL: APPLY Operator

Leave a Reply

Top