PERINTAH JOIN PADA ORACLE 10g

Perintah Join menggunakan syntax join ANSI atau SQL

A. Pengertian SQL

Structured Query Language (SQL) merupakan bahasa yang banyak digunakan dalam berbagai produk database. SQL dibangun di laboratorium IBM-San Jose California sekitar akhir tahun 70-an. Pertama kali dikembangkan sebagai bahasa di produk database DB2 yang sampai saat ini merupakan produk database andalan IBM. SQL sering di lafalkan dengan “sequel”. Saat ini organisasi standar America (ANSI) menetapkan standar bahasa SQL yaitu ANSI-92 standard.

Masing-masing vendor database memiliki bahasanya sendiri sebagaian besar spesifikasinya mengacu pada standar ANSI tersebut dengan berbagai ekstensi tambahan. SQL Server menggunakan bahasa Transact-SQL dalam produknya, sedangkan Oracle menggunakan PL/SQL. 

Penggabungan tabel dalam perintah SQL menggunakan keyword JOIN , berikut ini, merupakan perintah join dalam syntax SQL atau ANSI :

1.  INNER JOIN

Inner Join adalah perintah untuk operasi penggabungan yang paling umum digunakan dan biasanya dikatakan sebagai default join-type. Inner Join hanya menampilkan data yang benar-benar terdapat di dalam tabel yang saling dihubungkan.

Misalnya menggabungkan tabel Customers dengan Orders, maka field penghubung yang digunakan adalah CustomerID. 

Dalam teknik INNER JOIN maka hasil yang ditampilkan hanya record yang memiliki CustomerID sama di kedua tabel tersebut. Apabila terdapat customr yang CustomerID nya tidak ditemukan di tabel Orders maka data tersebut tidak ditampilkan.

Berikut ini contoh perintah yang menggunakan syntax inner join :

SELECT CompanyName, OrderID, OrderDate

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Perintah SQL tersebut mengambil field CompanyName dari tabel Customers, sedangkan field OrderID dan OrderDate diambil dari tabel Orders. Kunci utama penggabungan dua tabel tersebut adalah keyword INNER JOIN dengan kriteria CustomerID.

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Antara nama tabel dengan nama field dibatasi dengan tanda titik (.) misalnya Orders.CustomerID. Tujuan pencantuman nama tabel tersebut adalah menghindari ambiguitas yang mengakibatkan error apabila perintah tersebut dieksekusi. Karena CustomerID tersebut terdapat di kedua tabel maka nama tabel harus dicantumkan agar dapat diidentifikasi secara unik field mana yang dimaksud. Untuk memberikan gambaran lebih kompleks mengenai penerapan penggabungan tabel ini anda dapat memodifikasi contoh perintah yang terdapat penggunaan GROUP BY. Pada perintah tersebut anda menghitung Jumlah dan rataan produk yang terjual, tetapi hanya ditampilkan ProductID saja sedangkan nama produknya tidak terlihat karena terdapat di tabel lain. Anda dapat menggabungan tabel Order Details tersebut dengan tabel Products yang menyimpan nama produk. Dengan demikian dapat ditampilkan baik ProductID maupun nama produknya.

2.  LEFT JOIN

Berikut merupakan contoh perintah join dalam syntax left join :

SELECT Customers.CustomerID, Customers.CompanyName,

COUNT(Orders.OrderID) AS Frekuensi

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

GROUP BY Customers.CustomerID, Customers.CompanyName

ORDER BY COUNT(Orders.OrderID)

Karena menggunakan perintah LEFT JOIN maka semua data di tabel Customers ditampilkan seluruhnya

walaupun frekuensi ordernya 0

Kita dapat melihat terdapat 2 customer yang frekuensi 0 tetapi tetap ditampilkan. Di akhir tampilan hasil

tersebut terlihat ada 91 baris yang ditampikan. Untuk melakukan pengecekan jalankan perintah berikut :

SELECT COUNT(*) from dbo.Customers

Artinya terdapat 91customer di tabel Customers, yaitu sama dengan jumlah baris di perintah sebelumnya.

Dengan demikian memang benar bahwa LEFT JOIN telah menampilkan seluruh Customer yang berjumlah 91.

3.  RIGHT JOIN

Perintah RIGHT JOIN merupakan kebalikan dari LEFT JOIN, yaitu menampilkan semua isi tabel yang disebutkan kedua dalam perintah join. Dalam contoh di atas apabila LEFT JOIN diganti RIGHT JOIN maka semua isi tabel Orders akan ditampilkan semuanya.

Cobalah memodifikasi perintah diatas menjadi sebagai berikut :

SELECT Customers.CustomerID, Customers.CompanyName,

COUNT(Orders.OrderID) AS Frekuensi

FROM Customers

RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

GROUP BY Customers.CustomerID, Customers.CompanyName

ORDER BY COUNT(Orders.OrderID)

Setelah anda jalankan perintah tersebut lihatlah perbedaaanya dengan perintah LEFT JOIN sebelumnya. Maka terlihat hanya dihasilkan 89 baris, berarti terdapat 2 customer yang CustomerID nya tidak terdapat di table Orders atau frekuensi ordernya = 0.

4. FULL JOIN

Jenis join terakhir adalah FULL JOIN yang menampilkan semua data dari dua tabel yang dihubungkan meskipun terdapat data yang tidak memiliki pasangan di tabel lainnya.

Misalnya kita mengambil data dari tabel Country dengan tabel City menggunakan FULL JOIN. Data dari kedua tabel akan ditampilkan semuanya baik untuk nama kota yang tidak memiliki data negara maupun sebaliknya.

Manipulasi Data dengan SQL

Selain untuk mengambil informasi dari database anda juga dapat menggunakan perintah SQL untuk memanipulasi data. Proses tersebut meliputi menambah, menghapus, dan mengedit data.

Perintah manipulasi data sangat sering digunakan dalam aplikasi database dan bahakan dapat dikatakan menjadi inti sebuah aplikasi. Sebuah tabel dapat diisi dengan data, dihapus, maupun diedit datanya. Perintahperintah tersebut dilaksanakan berdasarkan kriteria tertentu menggunakan keyword WHERE, BETWEEN maupun LIKE.

Statement INSERT

Untuk mengisikan data ke dalam suatu tabel digunakan perntah INSERT yang memiliki syntax umum sebagai berikut :

INSERT table (column list)

VALUES (value list)

Misalnya untuk mengisikan data customer baru dituliskan perintah berikut :

INSERT Customers (CustomerID, CompanyName, ContactName)

VALUES ('MJTR', 'Majuterus', 'Lisha')

Perintah tersebut mengisikan data di tabel Customers untuk tiga kolom yaitu CustomerID, CompanyName

dan ContactName. Sedangkan kolom lain yang tidak diisi maka terisi dengan nilai default sesuai dengan desain tabelnya. Apabila desain tabelnya tidak mengijinkan nilai NULL maka anda harus mengisikan nilainya dalam perintah INSERT tersebut.

Apabila anda menampilkan data tabel Customers maka tampak data yang telah diisikan tersebut sebagai berikut :

CustomerID CompanyName ContactName

Apabila perintah INSERT digunakan untuk mengisikan seluruh kolom yang terdapat di suatu tabel maka nama kolom tidak perlu disebutkan secara eksplisit. Cukup disebutkan nilai data yang akan dimasukkan saja. Misalnya untuk mengisikan data ke tabel Shippers yang hanya terdiri dari tiga kolom dilancarkan perintah berikut :

INSERT Shippers

VALUES ('Megah Shipping', '021-55568953')

Anda mungkin bertanya mengapa VALUES yang diisikan hanya dua kolom, sedangan tabel Shippers terdiri dari 3 kolom. Jawabannya adalah karena kolom pertama yang bernama ShippersID telah disetting desainnya sebagai autonumber. Dengan demikian kolom tersebut akan terisi secara otomatis dengan angka berurut setiap terdapat data baru yang dimasukkan sehingga tidak perlu lagi diinsert secara eksplisit.

INSERT dari Tabel Lain

Pengisian tabel juga dapat menggunakan data yang diperoleh dari tabel lain. Caranya adalah dengan menggunakan perintah SELECT v berisi daftar data yang akan dimasukkan setelah perintah INSERT. Misalnya anda ingin memasukkan data di tabel Suppliers ke dalam tabel Customers maka digunakan perintah berikut :

INSERT Customers (CustomerID, CompanyName, ContactName)

SELECT SupplierID, CompanyName, ContactName

FROM Suppliers

WHERE Country = 'USA'

Apabila anda belum melakukan perubahan apapun di tabel Suppliers maka akan terdapat 4 supplier yang diisikan ke tabel Customers.

Perintah tersebut menggunakan kriteria WHERE Country = 'USA', sehingga hanya supplier yang berada di negara USA saja yang dimasukkan ke tabel Customers.

Prinsip utama dalam penggunaan perintah tersebut adalah jumlah kolom yang akan diisi harus sama dengan kolom yang diambil dalam daftar SELECT. Dengan demikian apabila dua buah tabel memiliki jumlah kolom yang sama maka anda dapat menggunakan perintah tersebut untuk mengisikan seluruh kolom dengan perintah sederhana tanpa menyebutkan kolmnya satu per satu.

Misalkan tabel Customers dan Suppliers memiliki jumlah kolom sama dan anda ingin memasukkan semua supplier ke tabel Customers maka dapat digunakan perintah sederhana sebagai berikut :

INSERT Customers

SELECT * FROM Suppliers

Statement DELETE

Statement DELETE merupakan kebalikan perintah INSERT. Perintah ini menghapus data yang terdapat di suatu tabel. Data dihapus per record atau per baris berdasarkan kriteria tertentu.

Penentuan kriteria record mana yang akan dihapus bisa dilakukan dengan menggunakan klausa WHERE.

Misalkan anda ingin menghapus data semua customer yang berada di negara France.

Syntax umum statement ini adalah sebagai berikut :

DELETE FROM table_name

WHERE Condition

Untuk menghapus data customer yang berasal dari Mexico di tabel Customers maka perintahnya adalah :

DELETE FROM Customers

where Country= 'Mexico'

Atau anda bisa juga menghapus data berdasarkan CustomerID yang merupakan primary key di tabel tersebut.

DELETE FROM Customers

where CustomerID= ‘ALFKI’

Selain menggunakan WHERE, dapat juga digabungkan dengan operator LIKE dan BETWEEN untuk membuat kriteria yang lebih fleksible. Misalnya sebagai berikut :

DELETE FROM Orders

WHERE OrderDate BETWEEN '07/01/1996' AND '07/31/1996'

Perintah tersebut akan menghapus data di tabel order yang memiliki OrderDate antara 1 Juli 1996 sampai dengan 31 Juli 1996.

Menghapus Seluruh Tabel

Apabila anda ingin mengosongkan tabel dan menghapus semua data yang ada di dalamnya maka digunakan perintah DELETE tanpa menggunakan kondisi WHERE.

Contoh berikut adalah perintah untuk mengosongkan isi tabel Products :

DELETE Products

Perintah tersebut hanya mengosongkan isi tabel saja tetapi tidak menghapus tabelnya.

Perlu diperhatikan apabila data dalam suatu tabel ternyata memiliki hubungan referential integrity dengan tabel lain maka penghapusan tersebut tidak dapat dilakukan. Mislanya apabila anda ingin menghapus data customer yang telah memiliki data order di tabel Orders, sedangakan antara kedua tabel tersebut memiliki hubungan referential integrity.

Untuk menghapus semua data di suatu tabel yang tidak memiliki hubungan referential integrity dengan table lain dapat digunakan perintah TRINCATE.

TRUNCATE TABLE Customers Maka perintah tersebut akan menghapus semua data customer yang tidak memiliki hubungan dengan table lain.

Statement UPDATE

Apabila anda ingin mengedit atau merubah suatu data tanpa menghapusnya maka digunakan perintah UPDATE . Perintah ini juga menggunakan kondisi tertentu dengan klausa WHERE sebagaimana perintah DELETE.

Syntax umum statement UPDATE adalah sebagai berikut :

UPDATE table_name

SET Column1 = Value1, Column2 = Value2, …..

WHERE condition

Perintah ini melakukan perubahan pada kolom tertentu sebagaimana yang disebutkan dalam perintah SET. Perubahan dilakukan terhadap record yang memenuhi kriteria di klausa WHERE.

Misalkan anda ingin menaikkan harga produk sebesar 10% untuk semua barang yang memiliki CategoryID = 2, maka digunakan perintah berikut :

UPDATE Products

SET UnitPrice = UnitPrice * 1.1

WHERE CategoryID = 2

Harga barang terdapat di kolom UnitPrice sehingga kolom tersebut dikalikan dengan 1.1. Perintah tersebut akan menaikkan harga semua barang yang terdapat di tabel Products sesuai dengan kriteria CategoryID. Selain menggunakan kriteria WHERE perintah ini juga dapat digabungkan dengan operator LIKE dan BETWEEN sebagaiman statement DELETE diatas.

Contoh berikut menggambarkan penggunaan beberapa kriteria untuk mengupdate suatu data :

UPDATE Customers

SET Country = 'Indonesia',

ContactName = 'Lisha'

WHERE City = 'Jakarta'

OR City = 'Surabaya'

Perintah diatas berguna untuk merubah nama negara menjadi Indonesia dan nama ContactName menjadi Lisha apabila kolom City berisi Jakarta atau Surabaya.

5.  SELF JOIN

Join yang dilakukan antar kolom dalam satu tabel.
Contoh :Select pegawai.first_name as pegawai, manager.first_name as manager
From employees pegawai
INNER JOIN  employees manager ON pegawai.manager_id=manager.employee.id;

6.  CROSS JOIN

CROSS JOIN mengembalikan produk Cartesian dari baris dari tabel dalam bergabung. Dengan kata lain, hal itu akan menghasilkan baris yang menggabungkan setiap baris dari tabel pertama dengan setiap baris dari tabel kedua.

Contoh eksplisit cross join:
SELECT *
FROM employee
CROSS JOIN department;

 

7.  NATURAL JOIN

Jenis equi-join mana predikat bergabung timbul implisit dengan membandingkan semua kolom di kedua tabel yang memiliki kolom yang sama-nama dalam tabel bergabung. Tabel bergabung dihasilkan hanya berisi satu kolom untuk setiap pasangan kolom sama bernama

Contoh : 

SELECT *

FROM employees

NATURAL JOIN department;

8.  LEFT OUTER JOIN

Menampilkan semua data yang ada di tabel kiri dan hanya data yang bersesuaian di tabel kanan, jika tabel kiri tidak mempunyai lawan di tabel kanan maka tabel kanan akan diisi dengan null.

Contoh :SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
Oracle mendukung alternatif syntax, yaitu :
SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID(+)
 

9.  RIGHT OUTER JOIN

Menampilkan semua data yang ada di tabel kanan dan hanya data yang bersesuaian di tabel kiri, jika tabel kanan tidak mempunyai lawan di tabel kiri maka tabel kiri akan diisi dengan null.

Contoh :SELECT *
FROM employee
RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Oracle mendukung alternatif syntax, yaitu:SELECT * FROM employee, department WHERE employee.DepartmentID(+) = department.DepartmentID
 

10.EQUI JOIN 

digunakan untuk menggabungkan dua tabel, suatu tabel sumber dan anggota, yang mempunyai nilai dari satu kolom atau lebih. Untuk record tabel sumber yang sesuai dengan tabel anggota, akan dimasukkan dalam hasil. Jika tidak ada kecocokan, data tersebut tidak dimunculkan dalam hasil. 

Contoh :
Select employee_id, department_id, department_name
From employees
JOIN departments ON
employees.department_id=departments.department_id;