Using Cross Apply and Outer Apply in User Defined Table Value Function

Posted at : Dec/24/2009

11513 Views | 1 Comments

Perintah APPLY di SQL digunakan untuk menampilkan data dari sebuah Table Value Function (TVF) dimana input parameter yang dibutuhkan di dalam fungsi tersebut didapatkan dari outer query yang digunakan untuk mengevaluasi data inner query dari TVF. Outer query dapat dianggap sebagai left table sedangkan inner query sebagai right table.

Penggunaan operator APPLY ini mirip dengan perintah JOIN. Terdapat dua jenis operator APPLY yaitu CROSS APPLY dan OUTER APPLY. Output dari operator CROSS APPLY sama seperti output dari perintah INNER JOIN dimana data yang dihasilkan merupakan data yang berasal dari left table dengan data yang berasal dari right table untuk nilai kolom yang bersesuaian, sehingga apabila di right table tidak terdapat data yang bersesuaian dengan data di left table maka data tersebut tidak akan ditampilkan. Sedangkan perintah OUTER APPLY sama seperti OUTER JOIN yang akan menampilkan semua data dari left table meskipun tidak terdapat data yang bersesuaian di right table.

Operator APPLY ini hanya dapat digunakan pada SQL Server 2005 atau versi diatasnya, dengan kata lain database compatibility level nya minimum harus 90.

Pada artikel ini saya akan membuat contoh penggunaan dari kedua perintah APPLY diatas terhadap TVF yang telah dibuat sebelumnya dan dibahas di artikel tentang Creating User Defined Table Value Function. Database yang digunakan yaitu Northwind, dan SQL Server yang digunakan versi 2008. Berikut langkah-langkahnya :

1. Ketikkan query berikut pada SQL Server Management Studio dan jalankan perintah tersebut sehingga hasilnya dapat dilihat pada gambar dibawah ini :

Select e.EmployeeID,f.FirstName,e.Country,f.OrderID,f.ProductName,f.Quantity
From Employees e 
Cross Apply dbo.GetEmployeeOrders(e.EmployeeID) As f
Order By e.EmployeeID,f.ProductName

Hasil eksekusi perintah CROSS APPLY diatas menghasilkan 2155 rows, dimana outer query memberikan input parameter berupa EmployeeID tiap record kepada TVF yang menghasilkan nilai-nilai dari kolom OrderID, ProductName dan Quantity berdasarkan EmployeeID yang diberikan.

2. Lakukan insert data baru ke table Employees :

Insert Into Employees(FirstName,LastName) Values('Rully','Yulian')

3. Ubah perintah CROSS APPLY diatas dengan OUTER APPLY dan hasilnya diperlihatkan pada gambar dibawah ini :

Select e.EmployeeID,f.FirstName,e.Country,f.OrderID,f.ProductName,f.Quantity
From Employees e 
Outer Apply dbo.GetEmployeeOrders(e.EmployeeID) As f
Order By e.EmployeeID,f.ProductName

Hasil eksekusi OUTER APPLY akan menambahkan satu record berupa record Employee yang baru saja ditambahkan. Nilai EmployeeID tersebut tetap ditampilkan meskipun tidak terdapat record yang bersesuaian di right table sebagai hasil eksekusi TVF. Apabila perintah OUTER diganti kembali dengan CROSS maka data EmployeeID tersebut tidak akan ditampilkan.