Query Relasi Antar Tabel MySQL Database

Query Relasi Antar Tabel MySQL Database - Pada artikel kali ini, eduloka akan membahas penggunaan dari statement SELECT ditanam dalam statement SELECT yang lain. Hasil dari statement SELECT bagian dalam / inner ( atau subselect ) digunakan di statement bagian luar untuk membantu menentukan isi dari hasil akhir. Sebuah subselect dapat digunakan pada klausa WHERE dan HAVING pada statement SELECT bagian luar, di mana itu disebut subquery atau query bersarang. Subselect juga bisa muncul di statement INSERT, UPDATE, dan DELETE.

image source: onpsikologi.com

Ada 3 Tipe Subquery:

1. Subquery skalar mengembalikan kolom tunggal dan baris tunggal; yaitu sebuah nilai tunggal. Prinsipnya, subquery skalar bisa di gunakan kapanpun nilai tunggal dibutuhkan. 
2. Subquery baris mengembalikan banyak kolom, tetapi hanya mengembalikan baris tunggal. Subquery baris digunakan kapanpun pembuat nilai baris dibutuhkan, khususnya di predikat.
3. Subquery table mengembalikan satu atau lebih kolom dan banyak baris. Subquery tabel bisa digunakan kapanpun table dibutuhkan, sebagai contoh, sebagai sebuah operan untuk predikat IN.

Contoh Subquery
Buat query untuk mendaftar staff yang bekerja di Branch yang terletak pada jalan '163 Main St'.

Jawab:

SELECT staff_no, f_name, l_name, position
FROM staff
WHERE branch_no = ( SELECT branch_no
FROM branch
WHERE street = '163 Main St');

Statement SELECT bagian dalam mencari nomor branch yang berkorespondensi ke branch dengan nama jalan '163 Main St' ( hanya akan ada satu nomor branch, jadi ini adalah sebuah contoh subquery skalar ). Setelah mendapatkan nomor branch, kemudian statement select bagian luar mengambil detail seluruh staff yang bekerja pada branch tersebut. Dengan kata lain, SELECT bagian dalam mengembalikan sebuah table hasil berisi nilai tunggal 'B003', berkorespondensi ke branch pada '163 Main St', dan SELECT bagian luar menjadi:

SELECT staff_no, f_name, l_name, position
FROM staff
WHERE branch_no = 'B003';

Tabel yang dihasilkan menjadi seperti berikut:

Latihan
Buatlah sebuah query yang mendaftar seluruh staff yang salarynya lebih dari rata-rata salary, dan menampilkan hanya yang memiliki salary lebih dari rata-rata salary.

Hasil dari query akan seperti berikut:

Aturan-aturan yang harus diperhatikan dalam menggunakan subquery:

• Klausa ORDER BY tidak dapat digunakan di subquery ( walaupun ORDER BY bisa digunakan di statement SELECT bagian paling luar ).
• Daftar SELECT subquery harus berisi nama kolom tunggal atau ekpresi, kecuali untuk subquery yang menggunakan kata kunci EXISTS.
• Pada dasarnya, nama kolom di dalam sebuah subquery merujuk ke nama table di dalam klausa FROM subquery. Ini juga memungkinkan untuk merujuk ke table di dalam klausa FROM dari query bagian luar dengan memberi syarat nama kolom.
• Ketika sebuah subquery adalah salah satu dari dua operan dilibatkan dalam sebuah perbandingan, subquery harus muncul pada bagian sebelah kanan dari perbandingan. Sebagai contoh, tidak akan benar untuk mengekpresikan subquery sebagai berikut:

SELECT staff_no, f_name, l_name, position, salary
FROM staff
WHERE (SELECT AVG(salary) FROM staff) < salary;
Karena subquery muncul pada bagian sebelah kiri dari perbandingan.

ANY dan ALL

ANY dan ALL dapat digunakan dengan subquery yang menampilkan kolom nomor tunggal. Jika subquery diawali dengan kata kunci ALL, kondisi akan benar jika kondisi dipenuhi oleh seluruh nilai yang dihasilkan oleh subquery. Jika subquery didahului oleh kata kunci ANY, kondisi akan benar jika kondisi dipenuhi oleh beberapa nilai yang dihasilkan oleh subquery. Jika subquery kosong, kondisi ALL akan mengembalikan nilai benar ( true ), sedangkan kondisi ANY mengembalikan nilai salah ( false ). Standar ISO juga membolehkan SOME untuk menggantikan tempat ANY.

Contoh penggunaan ANY/SOME

Temukan seluruh staff yang salarynya lebih besar dari gaji minimal satu anggota staff di cabang B003.

SELECT staff_no, f_name, l_name, position, salary
FROM staff
WHERE salary > SOME (SELECT salary
FROM staff
WHERE branch_no = 'B003');

Hasil dari table adalah sebagai berikut:

Penggunaan ALL

Menemukan seluruh staff yang salary lebih besar dari gaji setiap anggota staff di cabang.

Jawab:

SELECT staff_no, f_name, l_name, position, salary
FROM staff
WHERE salary > ALL (SELECT salary
FROM staff
WHERE branch_no = 'B003');

Dari beberapa query yang telah dibuat pada pertemuan sebelumnya terdapat batasan bahwa kolom yang muncul di tabel hasil berasal dari sebuah tabel tunggal. Dalam banyak kasus, ini tidaklah cukup. Untuk mengkombinasikan kolom dari beberapa tabel menjadi sebuah tabel kita memerlukan operasi join. Operasi join SQL mengkombinasikan informasi dari dua tabel dengan membentuk pasangan baris yang berhubungan dari dua buah tabel. Pasangan baris yang membuat tabel join adalah dimana kolom-kolom yang cocok dari masing-masing tabel yang memiliki nilai yang sama.

SIMPLE JOIN

Simple join disebut juga natural join. Untuk simple join data yang ditampilkan dalam tabel hasil hanya data yang memiliki kesamaan dalam kolom yang sama dari masing-masing table.

Contoh:
Mendaftar seluruh nama client yang telah melihat sebuah property dengan komentar yang diberikan.

Jawab:

SELECT c.client_no, f_name, l_name, property_no, comment_1
FROM client c, viewing_1 v
WHERE c.client_no = v.client_no;

Hasil dari query diatas adalah sebagai berikut:

Beberapa alternatif penulisan SQL dapat dilakukan dengan cara seperti berikut:

FROM client c JOIN viewing_1 v ON c.client_no = v.client_no
FROM client JOIN viewing_1 USING cleint_no
FROM client NATURAL JOIN viewing_

Dalam kasus diatas, klausa FROM menggantikan klausa awal FROM dan WHERE.

Latihan
Untuk masing-masing kantor cabang, daftarlah nomor dan nama staff yang mengatur properti dan properti yang mereka atur.

Tabel hasil yang ditampilkan adalah sebagai berikut:

Join Tiga buah Tabel

Contoh untuk join dari tiga buah tabel adalah sebagai berikut:

Untuk tiap-tiap cabang, daftarlah nomor dan nama staff yang memanage properti, termasuk kota di mana cabang berada dan properti yang staff atur.

SELECT b.branch_no, b.city, s.staff_no, f_name, l_name, property_no
FROM branch b, staff s, property_for_rent p
WHERE b.branch_no = s.branch_no AND s.staff_no = p.staff_no
ORDER BY b.branch_no, s.staff_no, property_no;

OUTER JOIN

Dari Simple Join di atas, data yang ditampilkan dalam tabel hasil adalah kombinasi dari data dari dua tabel dengan membentuk pasangan dari baris yang berelasi di mana kolom yang sama dari masing-masing tabel memiliki nilai yang sama. Jika satu baris dalam tabel tidak cocok, baris akan dihilangkan dari tabel hasil.

Standar ISO menyediakan operasi join yang lain disebut dengan outer join. Outer join menampilkan baris yang tidak memenuhi kondisi join. Untuk memahami outer join, kita buat dua buah tabel baru sebagai berikut:

branch_1

property_for_rent_1

Contoh dari Inner Join dari kedua table adalah sebagai berikut:

SELECT b.*, p.*
FROM branch_1 b, property_for_rent_1 p
WHERE b.b_city = p.p_city;

Hasil dari query join di atas adalah sebagai berikut:

Dari hasil tabel menunjukkan bahwa tidak ada hasil yang berhubungan dengan kantor di Bristol dan juga tidak ada baris yang berhubungan dengan property di Aberdeen. Jika ingin menambahkan baris yang tidak cocok ke dalam hasil, kita bisa menggunakan Outer Join. Outer Join terdiri dari tiga, yaitu: Left, Right, dan Full Outer Join.

Left Outer Join

Contoh:
Daftar seluruh kantor cabang dan beberapa property dalam kota yang sama.

SELECT b.*, p.*
FROM branch_1 b LEFT JOIN property_for_rent_1 p ON b.b_city = p.p_city;

Hasilnya adalah seperti berikut:

Hasil di atas tidak hanya menampilkan yang memiliki city yang sama tapi juga menampilkan baris dari tabel pertama ( kiri ) yang tidak cocok dengan baris dari tabel kedua ( kanan ). Kolom dari tabel kedua akan berisi NULL.

RIGHT OUTER JOIN

Contoh:
Daftar seluruh property dan beberapa kantor cabang dalam kota yang sama.

SELECT b.*, p.*
FROM branch_1 b RIGHT JOIN property_for_rent_1 p ON b.b_city = p.p_city;

Hasil dari query adalah sebagai berikut:

Right Outer Join memasukkan tidak hanya row yang memiliki city yang sama tapi juga baris dari tabel kedua ( kanan ) yang tidak memiliki kesamaan dengan baris dari tabel pertama ( kiri ).

FULL OUTER JOIN

Contoh:
Daftar kantor cabang dan properti dalam satu kota yang sama dengan termasuk cabang dan properti yang tidak cocok.

SELECT b.*, p.*
FROM branch_1 b FULL JOIN property_for_rent_1 p ON b.b_city = p.p_city;

Hasil dari query adalah sebagai berikut:

Full outer join tidak hanya menampilkan baris yang memiliki kesamaan, tetapi juga menampilkan baris yang tidak memiliki kesamaan.

Sekian artikel tentang Query Relasi Antar Tabel MySQL Database.

Related Posts

Query Relasi Antar Tabel MySQL Database

4/ 5

Oleh Unknown

Unknown 8/26/2015