Relasi Tabel MySQL: Foreign Key dan JOIN

Kenapa Database Membutuhkan Relasi?#

Bayangkan sebuah toko online mempunyai informasi berikut:

Sekilas tabel tersebut terlihat cukup sederhana.

Namun, bagaimana jika Andi melakukan sepuluh transaksi?

Nama Andi akan muncul berkali-kali.

Bagaimana jika alamat email Andi berubah?

Jika data pelanggan disalin ke banyak baris transaksi, perubahan informasi bisa menjadi lebih sulit dikelola dan berisiko menimbulkan inkonsistensi.

Database relasional membantu kita memisahkan informasi berdasarkan entitasnya.

Misalnya:

• Tabel pelanggan menyimpan informasi pelanggan.
• Tabel produk menyimpan informasi barang.
• Tabel pesanan menyimpan informasi pesanan.
• Tabel detail_pesanan menyimpan daftar produk dalam setiap pesanan.

Keempat tabel tersebut dapat saling terhubung melalui kolom tertentu.

Dengan begitu, informasi pelanggan tidak perlu disalin berulang-ulang ke setiap baris detail transaksi.

Mengenal Primary Key dan Foreign Key#

Sebelum membuat relasi, kita perlu memahami dua istilah penting.

1
id_pelanggan INT AUTO_INCREMENT PRIMARY KEY

Mengenal Jenis Relasi Tabel#

Ada beberapa pola hubungan yang sering digunakan dalam database relasional.

1
pelanggan (1) -------- (N) pesanan

1
pesanan (1) --- (N) detail_pesanan (N) --- (1) produk

Struktur Database Toko Online Kita#

Sampai Bab 4, kita sudah mempunyai dua tabel:

1
db_toko_bapakgabut
2
|
3
|-- pelanggan
4
|-- produk

Pada bab ini, kita akan menambahkan dua tabel:

1
db_toko_bapakgabut
2
|
3
|-- pelanggan
4
|-- produk
5
|-- pesanan
6
|-- detail_pesanan

Hubungannya adalah:

1
pelanggan
2
    |
3
    | 1 : N
4
    |
5
  pesanan
6
    |
7
    | 1 : N
8
    |
9
detail_pesanan
10
    |
11
    | N : 1
12
    |
13
  produk

Artinya, satu pelanggan bisa membuat banyak pesanan, satu pesanan bisa memiliki banyak detail, dan satu produk bisa digunakan dalam banyak detail pesanan.

Persiapan Sebelum Praktik#

Buka DBeaver dan hubungkan ke MySQL.

Kemudian jalankan:

1
USE db_toko_bapakgabut;

Pastikan tabel pelanggan dan produk sudah tersedia:

1
SHOW TABLES;

Kita akan menggunakan struktur tabel dari Bab 3 dan data yang sudah dimasukkan pada Bab 4.

Penting: Pada Bab 4, kita sempat mengubah harga dan stok beberapa produk serta menghapus Headset Gaming.

Karena itu, contoh pada bab ini tidak mengasumsikan bahwa seluruh data masih sama persis seperti saat pertama kali dimasukkan.

Kita akan mencari ID berdasarkan nama produk atau nama pelanggan terlebih dahulu.

Jika kamu menjalankan query INSERT dari bab sebelumnya lebih dari sekali, mungkin terdapat data dengan nama yang sama. Periksa data tersebut sebelum melanjutkan.

Untuk mengikuti contoh transaksi dengan mudah, pastikan tersedia masing-masing satu pelanggan bernama Andi dan Budi, serta masing-masing satu produk bernama Keyboard Mechanical, Mouse Wireless, dan Monitor 24 Inch.

Membuat Tabel Pesanan#

Tabel pesanan akan menyimpan informasi utama dari sebuah transaksi.

Jalankan query berikut:

1
CREATE TABLE pesanan (
2
    id_pesanan INT AUTO_INCREMENT PRIMARY KEY,
3
    id_pelanggan INT NOT NULL,
4
    tanggal_pesanan DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
5
    status_pesanan VARCHAR(20) NOT NULL DEFAULT 'baru',
6
    CONSTRAINT fk_pesanan_pelanggan
7
        FOREIGN KEY (id_pelanggan)
8
        REFERENCES pelanggan(id_pelanggan)
9
        ON UPDATE RESTRICT
10
        ON DELETE RESTRICT
11
) ENGINE=InnoDB;

Mari kita bahas bagian pentingnya.

id_pesanan merupakan primary key tabel pesanan.

id_pelanggan menyimpan referensi pelanggan yang membuat pesanan.

tanggal_pesanan mencatat waktu pembuatan pesanan.

status_pesanan menyimpan status sederhana, misalnya baru, diproses, atau selesai.

Bagian yang membuat relasi adalah:

1
CONSTRAINT fk_pesanan_pelanggan
2
    FOREIGN KEY (id_pelanggan)
3
    REFERENCES pelanggan(id_pelanggan)

Artinya, kolom id_pelanggan pada tabel pesanan mengacu pada kolom id_pelanggan di tabel pelanggan.

Kita juga menggunakan:

1
ON UPDATE RESTRICT
2
ON DELETE RESTRICT

Aturan tersebut mencegah perubahan atau penghapusan nilai referensi pada tabel induk jika masih terdapat baris anak yang bergantung padanya.

Untuk contoh toko online, aturan ini membantu menghindari penghapusan pelanggan yang masih memiliki pesanan.

Dalam aplikasi nyata, kebijakan penghapusan data perlu disesuaikan dengan kebutuhan bisnis dan ketentuan penyimpanan data.

Membuat Tabel Detail Pesanan#

Selanjutnya, kita membuat tabel untuk menyimpan daftar produk yang dibeli dalam sebuah pesanan.

1
CREATE TABLE detail_pesanan (
2
    id_detail INT AUTO_INCREMENT PRIMARY KEY,
3
    id_pesanan INT NOT NULL,
4
    id_produk INT NOT NULL,
5
    jumlah INT NOT NULL,
6
    harga_satuan DECIMAL(12,2) NOT NULL,
7
    CONSTRAINT fk_detail_pesanan
8
        FOREIGN KEY (id_pesanan)
9
        REFERENCES pesanan(id_pesanan)
10
        ON UPDATE RESTRICT
11
        ON DELETE RESTRICT,
12
    CONSTRAINT fk_detail_produk
13
        FOREIGN KEY (id_produk)
14
        REFERENCES produk(id_produk)
15
        ON UPDATE RESTRICT
16
        ON DELETE RESTRICT
17
) ENGINE=InnoDB;

Tabel ini memiliki dua foreign key.

Pertama:

1
FOREIGN KEY (id_pesanan)
2
REFERENCES pesanan(id_pesanan)

Kedua:

1
FOREIGN KEY (id_produk)
2
REFERENCES produk(id_produk)

Dengan demikian, setiap baris detail pesanan mengacu pada pesanan dan produk yang tersedia.

Memahami ON DELETE CASCADE dan RESTRICT#

Ketika membuat foreign key, MySQL menyediakan beberapa aturan untuk menangani perubahan atau penghapusan data yang direferensikan.

Dua aturan yang sering dibahas adalah RESTRICT dan CASCADE.

Misalnya, jika tabel pesanan menggunakan ON DELETE CASCADE terhadap pelanggan, menghapus pelanggan dapat ikut menghapus pesanan terkait.

Untuk data transaksi, perilaku seperti itu berisiko jika diterapkan tanpa mempertimbangkan kebutuhan bisnis.

Karena itu, pada tutorial ini kita menggunakan RESTRICT.

Ada juga aturan lain seperti SET NULL, tetapi penggunaannya harus sesuai dengan definisi kolom dan desain relasi.

Jangan memilih CASCADE hanya karena terlihat praktis.

Penghapusan data transaksi bukan sesuatu yang sebaiknya terjadi tanpa sengaja.

Menambahkan Data Pesanan#

Sekarang kita akan membuat pesanan untuk pelanggan yang sudah ada.

Pertama, cari ID pelanggan:

1
SELECT id_pelanggan, nama_pelanggan
2
FROM pelanggan
3
WHERE nama_pelanggan IN ('Andi', 'Budi');

Perhatikan hasilnya.

Jika terdapat beberapa pelanggan dengan nama sama, tentukan baris yang benar berdasarkan informasi lainnya.

Untuk contoh berikut, kita menggunakan variabel sesi MySQL agar tidak bergantung pada angka AUTO_INCREMENT tertentu.

Jalankan:

1
SET @id_andi = (
2
    SELECT id_pelanggan
3
    FROM pelanggan
4
    WHERE email = 'andi@example.com'
5
    LIMIT 1
6
);
7

8
SET @id_budi = (
9
    SELECT id_pelanggan
10
    FROM pelanggan
11
    WHERE email = 'budi@example.com'
12
    LIMIT 1
13
);

Variabel dengan awalan @ merupakan variabel sesi MySQL.

Nilainya dapat digunakan kembali selama sesi koneksi tersebut masih berlaku.

Pada contoh ini, LIMIT 1 digunakan agar subquery menghasilkan maksimal satu nilai. Namun, jika ada beberapa pelanggan dengan email yang sama, hasilnya tidak boleh dianggap sebagai identifikasi yang andal.

Pastikan email contoh tersebut hanya dimiliki oleh satu pelanggan.

Periksa variabel:

1
SELECT @id_andi AS id_andi, @id_budi AS id_budi;

Jika salah satu nilainya NULL, berarti pelanggan yang dicari tidak ditemukan.

Jangan lanjutkan INSERT sebelum masalah tersebut diperbaiki.

Sekarang buat pesanan:

1
INSERT INTO pesanan (id_pelanggan, status_pesanan)
2
VALUES
3
    (@id_andi, 'baru'),
4
    (@id_budi, 'baru');

Kita sudah membuat dua pesanan.

Satu untuk Andi dan satu untuk Budi.

1
SELECT
2
    p.id_pesanan,
3
    p.id_pelanggan,
4
    p.tanggal_pesanan,
5
    p.status_pesanan
6
FROM pesanan p
7
ORDER BY p.id_pesanan DESC;

1
SET @pesanan_andi = 1;
2
SET @pesanan_budi = 2;

Menambahkan Detail Pesanan#

Sekarang kita cari ID produk.

1
SELECT id_produk, nama_produk, harga
2
FROM produk
3
WHERE nama_produk IN (
4
    'Keyboard Mechanical',
5
    'Mouse Wireless',
6
    'Monitor 24 Inch'
7
);

Pastikan setiap nama produk mengarah pada produk yang dimaksud.

Untuk mempermudah latihan, kita akan mengambil ID produk melalui variabel.

1
SET @produk_keyboard = (
2
    SELECT id_produk
3
    FROM produk
4
    WHERE nama_produk = 'Keyboard Mechanical'
5
    LIMIT 1
6
);
7

8
SET @produk_mouse = (
9
    SELECT id_produk
10
    FROM produk
11
    WHERE nama_produk = 'Mouse Wireless'
12
    LIMIT 1
13
);
14

15
SET @produk_monitor = (
16
    SELECT id_produk
17
    FROM produk
18
    WHERE nama_produk = 'Monitor 24 Inch'
19
    LIMIT 1
20
);

Periksa hasilnya:

1
SELECT
2
    @produk_keyboard AS id_keyboard,
3
    @produk_mouse AS id_mouse,
4
    @produk_monitor AS id_monitor;

Jika semua ID tersedia, sekarang kita bisa menambahkan detail pesanan.

Kita akan membuat skenario:

• Andi membeli satu Keyboard Mechanical dan dua Mouse Wireless.
• Budi membeli satu Monitor 24 Inch.

Jalankan:

1
INSERT INTO detail_pesanan (
2
    id_pesanan,
3
    id_produk,
4
    jumlah,
5
    harga_satuan
6
)
7
VALUES
8
    (@pesanan_andi, @produk_keyboard, 1, 325000),
9
    (@pesanan_andi, @produk_mouse, 2, 135000),
10
    (@pesanan_budi, @produk_monitor, 1, 1650000);

Angka harga pada contoh ini merupakan harga transaksi simulasi yang kita tentukan sendiri.

Jadi, nilainya tidak harus sama dengan harga produk saat ini.

Pada aplikasi nyata, backend biasanya mengambil harga yang berlaku dari sumber tepercaya, menerapkan aturan diskon jika ada, lalu menyimpan harga transaksi secara konsisten.

Catatan: Jalankan INSERT contoh pesanan dan detail ini satu kali saja. Jika diulang, kamu dapat membuat transaksi duplikat.

Mengenal JOIN dalam SQL#

Sekarang kita sudah mempunyai empat tabel yang saling berhubungan.

Namun, bagaimana cara menampilkan nama pelanggan dari tabel pelanggan bersama informasi pesanan dari tabel pesanan?

Di sinilah kita menggunakan JOIN.

JOIN memungkinkan kita menggabungkan informasi dari beberapa tabel berdasarkan kondisi hubungan tertentu.

Jenis JOIN yang akan kita pelajari:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN

Kita juga akan melihat bagaimana menggabungkan lebih dari dua tabel.

INNER JOIN — Mengambil Data yang Memiliki Pasangan#

INNER JOIN hanya mengembalikan kombinasi baris yang memenuhi kondisi JOIN.

Misalnya, kita ingin menampilkan pesanan beserta nama pelanggannya.

1
SELECT
2
    p.id_pesanan,
3
    pl.nama_pelanggan,
4
    p.tanggal_pesanan,
5
    p.status_pesanan
6
FROM pesanan p
7
INNER JOIN pelanggan pl
8
    ON p.id_pelanggan = pl.id_pelanggan;

Perhatikan bagian:

1
FROM pesanan p
2
INNER JOIN pelanggan pl
3
    ON p.id_pelanggan = pl.id_pelanggan

p merupakan alias untuk tabel pesanan.

pl merupakan alias untuk tabel pelanggan.

Alias membantu kita menulis query lebih ringkas, terutama ketika beberapa tabel memiliki nama kolom yang sama.

Kondisi ON menentukan bagaimana kedua tabel dihubungkan.

Jika setiap pesanan memiliki pelanggan yang valid, hasilnya akan menampilkan pesanan bersama nama pelanggan.

1
SELECT
2
    pesanan.id_pesanan,
3
    pelanggan.nama_pelanggan
4
FROM pesanan
5
INNER JOIN pelanggan
6
    ON pesanan.id_pelanggan = pelanggan.id_pelanggan;

LEFT JOIN — Tetap Menampilkan Data dari Tabel Kiri#

Berbeda dengan INNER JOIN, LEFT JOIN mempertahankan seluruh baris dari tabel di sebelah kiri JOIN.

Jika tidak ditemukan pasangan di tabel kanan, kolom dari tabel kanan akan bernilai NULL.

Contohnya, kita ingin melihat semua pelanggan, termasuk pelanggan yang belum pernah membuat pesanan.

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan,
4
    p.id_pesanan,
5
    p.status_pesanan
6
FROM pelanggan pl
7
LEFT JOIN pesanan p
8
    ON pl.id_pelanggan = p.id_pelanggan
9
ORDER BY pl.id_pelanggan, p.id_pesanan;

Pada contoh ini, tabel kiri adalah pelanggan.

Artinya, seluruh pelanggan tetap ditampilkan.

Jika Citra dan Dina belum mempunyai pesanan, keduanya tetap muncul dengan informasi pesanan bernilai NULL.

Jika satu pelanggan memiliki beberapa pesanan, nama pelanggan tersebut dapat muncul lebih dari satu kali.

Itu bukan berarti JOIN salah.

Itu terjadi karena satu pelanggan memang mempunyai hubungan one-to-many dengan pesanan.

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan
4
FROM pelanggan pl
5
LEFT JOIN pesanan p
6
    ON pl.id_pelanggan = p.id_pelanggan
7
WHERE p.id_pesanan IS NULL;

1
IS NULL

RIGHT JOIN — Mempertahankan Data dari Tabel Kanan#

MySQL juga mendukung RIGHT JOIN.

Konsepnya mirip dengan LEFT JOIN, tetapi tabel yang seluruh barisnya dipertahankan berada di sebelah kanan.

Contoh:

1
SELECT
2
    pl.nama_pelanggan,
3
    p.id_pesanan
4
FROM pesanan p
5
RIGHT JOIN pelanggan pl
6
    ON p.id_pelanggan = pl.id_pelanggan;

Query tersebut mempertahankan seluruh pelanggan.

Dalam banyak kasus, RIGHT JOIN dapat ditulis ulang menjadi LEFT JOIN dengan menukar posisi tabel.

Karena itu, untuk konsistensi dan kemudahan membaca query, banyak pengembang lebih sering menggunakan LEFT JOIN.

Menggabungkan Empat Tabel Sekaligus#

Sekarang kita masuk ke contoh yang lebih menarik.

Kita ingin menampilkan informasi:

• Nomor pesanan.
• Nama pelanggan.
• Nama produk.
• Jumlah pembelian.
• Harga satuan.
• Subtotal.

Informasi tersebut tersebar di empat tabel.

Gunakan query berikut:

1
SELECT
2
    p.id_pesanan,
3
    pl.nama_pelanggan,
4
    pr.nama_produk,
5
    dp.jumlah,
6
    dp.harga_satuan,
7
    dp.jumlah * dp.harga_satuan AS subtotal
8
FROM pesanan p
9
INNER JOIN pelanggan pl
10
    ON p.id_pelanggan = pl.id_pelanggan
11
INNER JOIN detail_pesanan dp
12
    ON p.id_pesanan = dp.id_pesanan
13
INNER JOIN produk pr
14
    ON dp.id_produk = pr.id_produk
15
ORDER BY p.id_pesanan, dp.id_detail;

Mari kita pahami alurnya.

Pertama, query mengambil data dari tabel pesanan.

Kedua, menghubungkannya dengan tabel pelanggan untuk mendapatkan nama pelanggan.

Ketiga, menghubungkan pesanan dengan detail_pesanan untuk mengetahui produk dan jumlah yang dibeli.

Keempat, menghubungkan detail dengan tabel produk untuk mendapatkan nama produk.

Hasilnya kurang lebih seperti berikut, jika data latihan sesuai skenario:

Nomor pesanan yang sebenarnya mengikuti nilai AUTO_INCREMENT pada database masing-masing.

Perhatikan bagian:

1
dp.jumlah * dp.harga_satuan AS subtotal

MySQL menghitung subtotal dengan mengalikan jumlah barang dan harga satuan.

AS subtotal memberikan alias pada kolom hasil perhitungan.

Dengan query seperti ini, data dari empat tabel dapat ditampilkan menjadi satu laporan transaksi yang lebih mudah dibaca.

Menghitung Total Setiap Pesanan#

Setelah bisa menghitung subtotal, kita dapat menjumlahkannya berdasarkan pesanan.

Gunakan SUM dan GROUP BY:

1
SELECT
2
    p.id_pesanan,
3
    pl.nama_pelanggan,
4
    SUM(dp.jumlah * dp.harga_satuan) AS total_pesanan
5
FROM pesanan p
6
INNER JOIN pelanggan pl
7
    ON p.id_pelanggan = pl.id_pelanggan
8
INNER JOIN detail_pesanan dp
9
    ON p.id_pesanan = dp.id_pesanan
10
GROUP BY
11
    p.id_pesanan,
12
    pl.nama_pelanggan
13
ORDER BY p.id_pesanan;

Jika mengikuti contoh transaksi sebelumnya, hasil perhitungannya:

Perhitungannya:

Andi:

1
(1 × 325000) + (2 × 135000) = 595000

Budi:

1
1 × 1650000 = 1650000

SUM digunakan untuk menjumlahkan nilai.

GROUP BY digunakan untuk mengelompokkan baris berdasarkan kolom tertentu.

Kita baru mengenalkannya secara singkat di sini.

Pada Bab 6, kita akan membahas GROUP BY, HAVING, dan subquery dengan lebih mendalam.

Kesalahan Umum Saat Menggunakan JOIN#

JOIN merupakan fitur SQL yang sangat berguna, tetapi ada beberapa kesalahan yang perlu dihindari.

1
SELECT *
2
FROM pelanggan
3
CROSS JOIN produk;

1
SELECT
2
    pl.nama_pelanggan,
3
    p.id_pesanan
4
FROM pelanggan pl
5
LEFT JOIN pesanan p
6
    ON pl.id_pelanggan = p.id_pelanggan
7
WHERE p.status_pesanan = 'baru';

1
SELECT
2
    pl.nama_pelanggan,
3
    p.id_pesanan
4
FROM pelanggan pl
5
LEFT JOIN pesanan p
6
    ON pl.id_pelanggan = p.id_pelanggan
7
   AND p.status_pesanan = 'baru';

Latihan Mandiri#

Sekarang waktunya mencoba sendiri.

Gunakan database db_toko_bapakgabut yang sudah mempunyai empat tabel.

Pembahasan Latihan#

1
SELECT
2
    p.id_pesanan,
3
    pl.nama_pelanggan,
4
    p.tanggal_pesanan
5
FROM pesanan p
6
INNER JOIN pelanggan pl
7
    ON p.id_pelanggan = pl.id_pelanggan;

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan
4
FROM pelanggan pl
5
LEFT JOIN pesanan p
6
    ON pl.id_pelanggan = p.id_pelanggan
7
WHERE p.id_pesanan IS NULL;

1
SELECT
2
    pl.nama_pelanggan,
3
    pr.nama_produk,
4
    dp.jumlah,
5
    dp.harga_satuan,
6
    dp.jumlah * dp.harga_satuan AS subtotal
7
FROM detail_pesanan dp
8
INNER JOIN pesanan p
9
    ON dp.id_pesanan = p.id_pesanan
10
INNER JOIN pelanggan pl
11
    ON p.id_pelanggan = pl.id_pelanggan
12
INNER JOIN produk pr
13
    ON dp.id_produk = pr.id_produk;

1
SELECT
2
    pl.id_pelanggan,
3
    pl.nama_pelanggan,
4
    SUM(dp.jumlah * dp.harga_satuan) AS total_pembelian
5
FROM pelanggan pl
6
INNER JOIN pesanan p
7
    ON pl.id_pelanggan = p.id_pelanggan
8
INNER JOIN detail_pesanan dp
9
    ON p.id_pesanan = dp.id_pesanan
10
GROUP BY
11
    pl.id_pelanggan,
12
    pl.nama_pelanggan
13
ORDER BY total_pembelian DESC;

Kesimpulan#

Pada Bab 5 ini, kita sudah mempelajari konsep penting dalam database relasional.

Primary key digunakan untuk mengidentifikasi baris secara unik.

Foreign key digunakan untuk menjaga hubungan referensial antartabel.

Relasi one-to-many memungkinkan satu baris induk memiliki banyak baris anak.

Relasi many-to-many dapat diimplementasikan melalui tabel penghubung.

Kita juga sudah mempelajari bagaimana INNER JOIN, LEFT JOIN, dan RIGHT JOIN bekerja.

Dengan JOIN, informasi yang tersimpan pada beberapa tabel dapat digabungkan menjadi laporan yang lebih bermakna.

Database toko online kita sekarang memiliki empat tabel yang saling terhubung:

• pelanggan
• produk
• pesanan
• detail_pesanan

Pada Bab 6, kita akan mempelajari query SQL lanjutan menggunakan GROUP BY, HAVING, dan subquery.

Kita akan mencoba membuat laporan penjualan, menghitung total transaksi, dan mengambil informasi berdasarkan hasil query lainnya.

Karena setelah toko online berhasil mencatat transaksi, tentu pemilik toko ingin tahu berapa banyak barang yang terjual dan siapa pelanggan yang paling rajin belanja.

Sampai jumpa di artikel berikutnya, Gabuters!