SQL Pada Multi Table dan Bertingkat

Basis Data Kelas XII

A. Select Multitable

SELECT merupakan perintah DML (Data Manipulation Language) untuk menghasilkan recordset dengan mengekstrak data dari database. Perintah SELECT ini sering dipakai dan memiliki bentuk yang kompleks. Bentuk sederhana dari SELECT sebagai berikut.

SELECT select_list [INTO new_table] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC]

misalnya pada tabel pelanggan dengan field(kolom) nama, alamat, kota, negara, dan telepon.

SELECT Nama, Alamat, telepon FROM pelanggan

apabila ingin menampilkan seluruh field pada tabel pelanggan data menggunakan perintah asterik(*) sebagai berikut.

SELECT * FROM pelanggan

B. Select Bertingkat(subQuery)

Fungsi dari subQuery

Menyalin data dari satu tabel ke tabel yang lainnya.
Menghapus baris dari satu tabel berdasarkan baris dari tabel lain.
Mengambil data dari tabel lain untuk di update ke tabel yang dituju.
Untuk menyelesaikan persoalan dimana terdapat suatu nilai yang tidak diketahui.
Menerima data dari inline view.

Bentuk umum dari subquery berjenjang

Secara default, nama-nama kolom di subquery mengacu ke nama table diklausa FROM di query lebih luar dengan mengkualifikasi nama kolom
Saat subquery adalah salah satu dua operan dilibatkan di pembandingan, subquery harus muncul di sisi kanan pembandingan.
Subquery tdak boleh digunakan sebagai operan di ekspresi.
Subquery bisa diletakkan pada field list atau pada klausa WHERE atau HAVING, dimana subQuery tersebut menyediakan satu atau lebih nilai yang diperiksa.
Didalam WHERE dan HAVINGsubquery memiliki tiga bentuk diantaranya:

Expression [NOT] IN (subquery)
Comparison [ANY|ALL|SOME] (subquery)
[NOT] EXISTS (subquery).

Command ANY diikuti dengan operatorperbandingan memiliki arti menampilkannilai yang sesuai dengan apapun yangdihasilkan oleh sub query. ANY berbedadengan IN, jika IN itu semua data,sedangkan ANY hanya beberapa data.Contoh query dasar dari sub query ANY

Query Berjenjang

Syntaks dan Beberapa penggunaan subQuery

subQuery dengan ANY

Command ANY diikuti dengan operator perbandingan memiliki arti menampilkan nilai yang sesuai dengan apapun yan dihasilkan oleh subQuery. ANYberbeda dengan IN, jikaIN itu semua data, sedangkan ANY hanya beberapa data. Contoh query dasar dari sub query ANY :
SELECT nama kolom FROMnamatabelWHERE kondisiopeatorperbandinganANY (subquery);

subQuery dengan "ALL"

Command ALL diikuti dengan operator perbandingan digunakan memiliki arti menampilkan nilai jika perbandingan bernilai benar untuk semua data. Operator perbandingan tersebut berupa ( <, >, =, !=). Query dasar dari subQuery ALL

SELECT namakolom FROMnamatabel WHERE kondisiopeatorperbandingan ALL (subquery);

subQuery dengan "IN"

Jika operator '=' hanya digunakan untuk hasil yang tepat satu, maka jika ingin menampilkan yang memiliki hasil lebih dari satu maka dapat menggunakan perintah IN. Dan struktur query yang digunakan dalam hal ini adalah

SELECTnamakolomFROMnamatabelWHEREkondisi opeatorperbandinganIN (subquery);

Single Row Subquery

Single row subquery memberikan hasil hanya satu baris pada bagian subquery. Untuk single row subquery ini yang digunakan adalah operator pembanding: , >, >=, <, <= atau <>
Contoh:
Menampilkan data karyawan yang memiliki jabatan sama dengan Smith.
SELECT last_name, title FROM employee WHERE title = ( SELECT title FROM employee WHERE last_name = ‘Smith’ );

Kita dapat menampilkan data dari query utama dengan menggunakan fungsi grup (group function) untuk menghasilkan satu baris data.

Contoh:
Menampilkan data karyawan yang memiliki gaji dibawah rata-rata.
SELECT last_name, title, salary FROM employee WHERE salary < ( SELECT AVG(salary) FROM employee );

Multiple Rows Subquery

Multiple Row Subquery adalah subquery yang menghasilkan lebih dari satu baris data. Untuk multiple row subquery ini yang digunakan adalah operator pembanding IN, ANY atau ALL.
Contoh:
Menampilkan data karyawan yang bekerja pada departemen Finance atau pada region 2.

SELECT last_name, first_name, titleFROM employee WHERE dept_id IN ( SELECT id FROM department WHERE name = ‘Finance’ OR region_id = 2 );

Contoh:
Menampilkan nama, gaji, dan nomer department dari pegawai yang gajinya sama dengan gaji minimum pada suatu department.

SELECT ename, sal, deptno FROMENP WHERE sal IN (SELECT MIN (sal) FROM ENP GROUP BY deptno)

Kesalahan dalam Subquery

Kesalahan umum dalam subquery adalah lebih dari satu baris data dihasilkan untuk single row subquery.
Contoh:
Subquery ini menghasilkan lebih dari satu baris data dan menggunakan single row operator. Kita dapat membetulkan kesalahan ini dengan mengubah = menjadi IN.

SELECT last_name, first_name, title

FROM employee WHERE dept_id = ( SELECT id FROM department WHERE name = ‘Finance’

OR region_id = 2 );

#1. INNER JOIN

INNER JOIN adalah tipe join yang akan kita bahas pertama. Tipe join ini akan mengambil semua row dari table asal dan table tujuan dengan kondisi nilai key yang terkait saja, dan jika tidak maka row tersebut tidak akan muncul.

Kalau tidak terdapat kondisi key terkait antar table, maka semua row dari kedua table dikombinasikan.

Contoh:

SELECT * FROM karyawan INNER JOIN gaji 

ON karyawan.karyawan_id=gaji.karyawan_id;

Query diatas akan menghasilakn output seperti berikut:

Coba Anda perhatikan jumlah record yang ditampilkan!

Ya, record yang ditampilkan sebanyak 5 record. Karena INNER JOIN hanya memperhitungkan kondisi key yang terkait antara table karyawan dengan table gaji.Sedangkan karyawan dengan karyawan_id=’006’ tidak ditampilkan, karena tidak terkait dengan table gaji.

Jika dibuat diagram venn-nya akan terlihat seperti gambar berikut:

#2. LEFT JOIN

LEFT JOIN atau biasa juga dikenal dengan LEFT OUTER JOIN merupakan perintah join untuk menampilkan semua data sebelah kiri dari table yang di joinkan dan menampilkan data sebelah kanan yang cocok dengan kondisi join. Jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis.

Contoh:

SELECT * FROM karyawan LEFT JOIN gaji 

ON karyawan.karyawan_id=gaji.karyawan_id;

Query diatas akan menghasilkan output seperti berikut:

Coba Anda perhatikan jumlah record yang ditampilkan!

Ya, record yang ditampilkan sebanyak 6 record. Karena LEFT JOIN akan menampilkan semua table sebelah kiri dari kondisi join yaitu table karyawan.

Semua data pada table karyawan akan ditampilkan, meskipun tidak ada kecocokan key pada table gaji.

Jika dibuat diagram venn-nya akan terlihat seperti gambar berikut:

Selain kondisi diatas, LEFT JOIN juga bisa menampilkan data yang hanya kondisi key pada table tamu (foreign key) kosong (NULL).

Contoh:

SELECT * FROM karyawan LEFT JOIN gaji 

ON karyawan.karyawan_id=gaji.karyawan_id 

WHERE gaji.karyawan_id IS NULL;

Query diatas akan menghasilkan output seperti berikut:

Data yang ditampilkan hanya 1 record. Hal ini dikarenakan, hanya ada satu data yang belum memiliki kecocokan key pada table tamu.

Untuk mempermudah anda memahami perbedaan antara kedua LEFT JOIN ini coba perhatikan diagram venn berikut:

Dengan melihat perbedaan dari diagram venn tersebut, maka Anda akan mudah memahami bagaimana left join ini bekerja.

Ingat LEFT JOIN ini sangat penting untuk anda pahami, karena disaat Anda mulai mengerjakan project yang cukup kompleks, maka anda akan banyak berkutat dengan left join ini.

Contoh pada kasus diatas, hanya dengan memanfaatkan left join kita bisa menampilkan semua data karyawan yang sudah ada gajinya dan siapa yang belum ada gajinya.

Selain itu anda juga dapat menampilkan semua data karyawan yang belum ada gajinya dengan fungsi LEFT JOIN WHERE NULL.

#3. RIGHT JOIN

Kebalikan dari LEFT JOIN adalah RIGHT JOIN, atau biasa juga dikenal dengan RIGHT OUTER JOIN. RIGHT JOIN akan menampilkan semua data yang ada di table sebelah kanan dan mencari kecocokan key pada table sebelah kiri.

Jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis pada table sebelah kiri.

Contoh:

SELECT * FROM gaji RIGHT JOIN karyawan 

ON gaji.karyawan_id=karyawan.karyawan_id;

Query diatas akan menampilkan output seperti gambar berikut:

Pada output diatas, anda dapat melihat bahwa terdapat NULL pada table sebelah kiri. Hal ini dikarenakan tidak ditemukan kecocokan key diantara kedua table.

Untuk lebih mudah memahaminya, perhatikan diagram venn berikut:

Selain kondisi diatas, RIGHT JOIN juga bisa menampilkan data yang hanya kondisi key pada table tamu (foreign key) kosong (NULL).

Contoh:

SELECT * FROM gaji RIGHT JOIN karyawan 

ON gaji.karyawan_id=karyawan.karyawan_id 

WHERE gaji.karyawan_id IS NULL;

Query diatas akan menghasilkan output seperti gambar berikut:

Data yang ditampilkan hanya 1 record. Hal ini dikarenakan, hanya ada satu data yang belum memiliki kecocokan key pada table tamu.

Untuk mempermudah anda memahami perbedaan antara kedua RIGHT JOIN ini coba perhatikan diagram venn berikut:

Perhatikan diagram venn diatas, dan bandingkan dengan diagram venn sebelumnya.

KESIMPULAN:

Pembahasan kali ini adalah tentang JOIN pada MySQL.

Terdapat tiga macam JOIN yaitu: INNER JOIN, LEFT JOIN, dan RIGHT JOIN.

LEFT JOIN merupakan perintah join untuk menampilkan semua data sebelah kiri dari table yang di joinkan dan menampilkan data sebelah kanan yang cocok dengan kondisi join. Jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis.

Kebalikan dari LEFT JOIN adalah RIGHT JOIN. RIGHT JOIN akan menampilkan semua data yang ada di table sebelah kanan dan mencari kecocokan key pada table sebelah kiri, jika tidak ditemukan kecocokan, maka akan di set NULL secara otomatis pada table sebelah kiri.

>>Semoga Bermanfaat<<