Belajar MySQL : Mengenal SQL Multitable dan Subquery

Di dalam suatu DBMS termasuk MySQL, tentunya sudah menjadi suatu kewajaran jika dalam satu database dapat terdiri dari beberapa tabel. Masing-masing tabel tersebut dapat berhubungan (berelasi) satu sama lain. Relasi antar-tabel dapat berupa relasi 1-1 (one-to-one), 1-M (one-to-many), atau M-N (many-to-many). Untuk menggabungkan 2 (dua) atau lebih tabel, dapat menggunakan bentuk perintah JOIN

Pada contoh kali ini, saya menggunakan empat tabel, yaitu: tabel pelanggan, produk, transaksi, dan transaksi_detail. struktur dan hubungan keempat tabel tersebut tampak seperti pada gambar berikut:


Dari keempat tabel tersebut, saya hanya menggunakan beberapa diantaranya. Adapun contoh datanya adalah sebagai berikut:

Tabel barang:

+-----------+-------------+-------------+---------+------+
| id_barang | id_kategori | nama_barang | harga   | stok |
+-----------+-------------+-------------+---------+------+
|         1 |           1 | RAM         |  230000 |    4 |
|         2 |           1 | Mainboard   | 1250000 |    7 |
|         3 |           1 | Mouse       |   80000 |    6 |
|         4 |           3 | Mousepad    |   35000 |    3 |
|         5 |           3 | Keyboard    |   80000 |    5 |
+-----------+-------------+-------------+---------+------+

Tabel pelanggan:

+--------------+---------+-------------------+
| id_pelanggan | nama    | email             |
+--------------+---------+-------------------+
|            1 | Alfa    | alfa@yahoo.com    |
|            2 | Beta    | beta@yahoo.com    |
|            3 | Charlie | charlie@gmail.com |
|            4 | Delta   | delta@gmail.com   |
+--------------+---------+-------------------+

Tabel penjualan:

+--------------+--------------+---------------+-----------------+
| id_transaksi | id_pelanggan | tgl_transaksi | total_transaksi |
+--------------+--------------+---------------+-----------------+
|            1 |            1 | 2017-02-22    |          230000 |
|            2 |            3 | 2017-02-22    |          195000 |
|            3 |            2 | 2017-01-01    |         1710000 |
|            4 |            1 | 2017-02-04    |          310000 |
|            5 |         NULL | 2017-02-10    |           80000 |
+--------------+--------------+---------------+-----------------+

I. JOIN Pada MySQL

Untuk menggabungkan tabel pada MySQL, gunakan klausa JOIN. Pada MySQL terdapat dua macam bentuk join, yaitu INNER JOIN, LEFT OUTER JOIN, dan RIGHT OUTER JOIN. Format penulisannya adalah sebagai berikut:

SELECT nama_kolom
FROM tabel
INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN tabel ON kondisi

Selain menggunakan klausa ON untuk mendefinisikan kondisi, dapat menggunakan klausa USING, format penulisannya adalah:

SELECT nama_kolom
FROM tabel
INNER JOIN | LEFT OUTER JOIN | RIGHT OUTER JOIN tabel USING(nama_kolom)

INNER atau CROSS JOIN

Cara pertama untuk menggabungkan tabel adalah menggunakan inner join. Dengan inner join, tabel akan digabungkan berdasarkan data yang sama, yang ada pada kedua tabel. Sebagai contoh saya akan menampilkan data pelanggan yang melakukan pesanan, query yang saya jalankan:

SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
JOIN penjualan pn ON pl.id_pelanggan = pn.id_pelanggan

Jika menggunakan klausa USING, maka query akan berbentuk seperti berikut:

SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
JOIN penjualan pn USING(id_pelanggan)

Hasil:

+--------------+---------+---------------+-----------------+
| id_pelanggan | nama    | tgl_transaksi | total_transaksi |
+--------------+---------+---------------+-----------------+
|            1 | Alfa    | 2017-02-22    |          230000 |
|            3 | Charlie | 2017-02-22    |          195000 |
|            2 | Beta    | 2017-01-01    |         1710000 |
|            1 | Alfa    | 2017-02-04    |          310000 |
+--------------+---------+---------------+-----------------+

OUTER JOIN

Cara kedua untuk menggabungkan tabel pada MySQL adalah menggunakan outer join. Pada outer join, data pada salah satu tabel akan ditampilkan semua, sedangkan data pada tabel yang lain hanya akan ditampilkan jika data tersebut ada pada tabel pertama.

    • LEFT OUTER JOIN

    Pada LEFT OUTER JOIN, semua data pada tabel sebelah kiri akan ditampilkan, sedangkan data pada tabel disebelah kanan hanya akan ditampilkan jika data terkait pada tabel tersebut muncul di tabel sebelah kiri.

Contoh tampilkan semua data pelanggan beserta data transaksinya, jalankan query berikut:

SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
LEFT JOIN penjualan USING(id_pelanggan)

Hasil:

+--------------+---------+---------------+-----------------+
| id_pelanggan | nama    | tgl_transaksi | total_transaksi |
+--------------+---------+---------------+-----------------+
|            1 | Alfa    | 2017-02-22    |          230000 |
|            3 | Charlie | 2017-02-22    |          195000 |
|            2 | Beta    | 2017-01-01    |         1710000 |
|            1 | Alfa    | 2017-02-04    |          310000 |
|            4 | Delta   | NULL          |            NULL |
+--------------+---------+---------------+-----------------+

    • RIGHT OUTER JOIN

Kebalikan dari LEFT OUTER JOIN, pada RIGHT OUTER JOIN, data pada tabel sebelah kanan akan ditampilkan semua, sedangkan data pada sebelah kiri hanya ditampilkan jika data terkait pada tabel tersebut muncul pada tabel sebelah kanan.

Contoh tampilkan semua data transaksi beserta data pelanggannya, jalankan query berikut:

SELECT pl.id_pelanggan, nama, tgl_transaksi, total_transaksi
FROM pelanggan pl
RIGHT JOIN penjualan USING(id_pelanggan)

Hasil:

+--------------+---------+--------------+---------------+-----------------+
| id_pelanggan | nama    | id_transaksi | tgl_transaksi | total_transaksi |
+--------------+---------+--------------+---------------+-----------------+
|            1 | Alfa    |            1 | 2017-02-22    |          230000 |
|            1 | Alfa    |            4 | 2017-02-04    |          310000 |
|            2 | Beta    |            3 | 2017-01-01    |         1710000 |
|            3 | Charlie |            2 | 2017-02-22    |          195000 |
|         NULL | NULL    |            5 | 2017-02-10    |           80000 |
+--------------+---------+--------------+---------------+-----------------+

IMPLISIT JOIN

Selain menggunakan klausa JOIN, terdapat satu cara lagi untuk menggabungkan tabel MySQL, yaitu menggunakan implisit join, disebut implisit join karena saya tidak menggunakan klausa JOIN, pada implisit join, kriteria hubungan antar tabel di definisikan pada klausa WHERE.

Sebagai contoh, saya gabungkan tabel pelanggan dan penjualan, jalankan query berikut:

SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
FROM pelanggan pl, penjualan pn
WHERE pl.id_pelanggan = pn.id_pelanggan

Hasil yang saya peroleh:

+--------------+---------+--------------+---------------+-----------------+
| id_pelanggan | nama    | id_transaksi | tgl_transaksi | total_transaksi |
+--------------+---------+--------------+---------------+-----------------+
|            1 | Alfa    |            1 | 2017-02-22    |          230000 |
|            3 | Charlie |            2 | 2017-02-22    |          195000 |
|            2 | Beta    |            3 | 2017-01-01    |         1710000 |
|            1 | Alfa    |            4 | 2017-02-04    |          310000 |
+--------------+---------+--------------+---------------+-----------------+

Pada bentuk klausa JOIN, hubungan antar tabel dinyatakan pada klausa ON atau USING, sedangkan filter datanya dilakukan pada klausa WHERE, misal:

SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
FROM pelanggan pl
LEFT JOIN penjualan pn USING (id_pelanggan)
WHERE pl.id_pelanggan = 2 OR pl.id_pelanggan = 1

sedangkan pada implisit JOIN, hubungan antar tabel dan filter datanya, semua didefinisikan pada klausa WHERE, misal:

SELECT pl.id_pelanggan, nama, id_transaksi, tgl_transaksi, total_transaksi
FROM pelanggan pl, penjualan pn
WHERE pl.id_pelanggan = pn.id_pelanggan
AND (pl.id_pelanggan = 2 OR pl.id_pelanggan = 1)

Sub Query Basis Data

Subquery adalah perintah SELECT yang berada di dalam perintah SQL lain. Subquery sangat berguna ketika ingin menampilkan data dengan kondisi yang bergantung
pada data di dalam table itu sendiri.

Kegunaan-kegunaan  Subquery dalam memanipulasi data:

    • Meng-copy data dari satu tabel ke tabel lain
    • Menerima data dari inline view
    • Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju
    • Menghapus baris dari satu tabel berdasarkan baris dari tabel lain.

Sintaks:

SELECT select_list
FROM table
WHERE expr operator
( SELECT select_list FROM table );

Contoh: Menampilkan data karyawan yang bekerja satu departemen dengan Biri.

SELECT last_name, title
FROM employee
WHERE dept_id =
( SELECT dept_id
FROM employee
WHERE UPPER(last_name) = ‘BIRI’ );

Operator EXIST dan NOT EXIST

Kata kunci EXIST dan NOT EXIST dirancang hanya untuk digunakan di subquery. Kata kunci-kata kunci ini menghasilkan nilai TRUE atau FALSE EXIST akan mengirim nilai TRUE jika dan hanya jika terdapat sedikitnya satu baris di table hasil yang dikirim oleh subquery. EXIST mengirim nilai FALSE jika subquery mengirm table kosong NOT EXIST kebalian dan EXIST. Karena EXIST dan NOT EXIST hanya memeriksa keberadaan baris-baris di table hasil subquery.

Contoh : Penggunaan EXIST

Daftarkan semua staf yang bekerja dikantor cabang beralamat di jalan “Tamansari 81”

SELECT IDStaf , namaDepan, namaBelakang, pangkat, gPokok
FROM Staf s
WHERE EXIST
(SELECT *
FROM KantorCabang k, StafKCabang sk
WHERE s.IDStaf=sk.IDStafAND
sk.IDKCabang =k.IDKCabang AND
k.jalan=’tamansari 81’
);

Operator Any(Some)

Operator Any (some) hampir sama penggunaannya seperti Exists. Tetapi operator relasi yang digunakan biasanya selain = (sama dengan). hal tersebut disebabkan apabila operator relasi = yang digunakan, maka sebetulnya fungsi operator Any (some) sama seperti operator IN, sehingga kondisi seperti itu tidak dianjurkan karena lebih mudah pemahamannya apabila menggunakan operator IN.

Contoh :
Akan menampilkan daftar nama pegawai dan gaji yang gajinya tidak paling sedikit :

select nama, gaji from pegawai where gaji > any (select gaji from pegawai); 

Operator All

Operator all digunakan untuk melakukan pembandingan dengan sub query. Kondisi dengan all menghasilkan nilai benar jika pembandingan menghasilkan benar untuk setiap nilai dalam sub query.

Contoh :
Akan menampilkan nama dan gaji pegawai yang gajinya lebih rendah daripada semua pegawai yang pekerjaannya 'SALESMAN' :

select nama where gaji < all (select gaji from pegawai where pekerjaan = 'SALESMAN'); 

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, title FROM employee WHERE dept_id IN ( SELECT id FROM department
WHERE name = ‘Finance’ OR region_id = 2 );

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’ );