Joins & Relationships
Menggabungkan data dari beberapa tabel dengan JOIN
Konsep Relasi Tabel
Database relasional menyimpan data dalam beberapa tabel yang saling berhubungan melalui key:
1. Primary Key (PK)
Kolom unik yang mengidentifikasi setiap baris dalam tabel.
2. Foreign Key (FK)
Kolom yang merujuk ke primary key di tabel lain, membentuk relasi.
Jenis Relasi
- One-to-One: Satu baris di tabel A berhubungan dengan tepat satu baris di tabel B
- One-to-Many: Satu baris di tabel A berhubungan dengan banyak baris di tabel B
- Many-to-Many: Banyak baris di tabel A berhubungan dengan banyak baris di tabel B (memerlukan tabel junction)
Studi Kasus: Database Sekolah
Kita akan menggunakan contoh database sekolah dengan beberapa tabel:
1. Tabel Siswa
CREATE TABLE siswa (
id INT AUTO_INCREMENT PRIMARY KEY,
nama VARCHAR(100) NOT NULL,
kelas_id INT,
FOREIGN KEY (kelas_id) REFERENCES kelas(id)
);
2. Tabel Kelas
CREATE TABLE kelas (
id INT AUTO_INCREMENT PRIMARY KEY,
nama_kelas VARCHAR(10) NOT NULL,
wali_kelas VARCHAR(100)
);
3. Tabel Mata Pelajaran
CREATE TABLE mata_pelajaran (
id INT AUTO_INCREMENT PRIMARY KEY,
nama_mapel VARCHAR(50) NOT NULL,
kode_mapel VARCHAR(10) UNIQUE
);
4. Tabel Nilai (Many-to-Many)
CREATE TABLE nilai (
id INT AUTO_INCREMENT PRIMARY KEY,
siswa_id INT,
mapel_id INT,
nilai DECIMAL(5,2),
semester TINYINT,
FOREIGN KEY (siswa_id) REFERENCES siswa(id),
FOREIGN KEY (mapel_id) REFERENCES mata_pelajaran(id)
);
Jenis-Jenis JOIN
1. INNER JOIN
Menampilkan baris yang memiliki kecocokan di kedua tabel.
SELECT siswa.nama, kelas.nama_kelas
FROM siswa
INNER JOIN kelas ON siswa.kelas_id = kelas.id;
-- Dengan alias tabel
SELECT s.nama, k.nama_kelas
FROM siswa s
INNER JOIN kelas k ON s.kelas_id = k.id;
2. LEFT JOIN (LEFT OUTER JOIN)
Menampilkan semua baris dari tabel kiri (pertama) dan baris yang cocok dari tabel kanan.
-- Menampilkan semua siswa bahkan jika tidak punya kelas
SELECT s.nama, k.nama_kelas
FROM siswa s
LEFT JOIN kelas k ON s.kelas_id = k.id;
3. RIGHT JOIN (RIGHT OUTER JOIN)
Menampilkan semua baris dari tabel kanan (kedua) dan baris yang cocok dari tabel kiri.
-- Menampilkan semua kelas bahkan jika tidak ada siswanya
SELECT s.nama, k.nama_kelas
FROM siswa s
RIGHT JOIN kelas k ON s.kelas_id = k.id;
4. FULL JOIN (FULL OUTER JOIN)
Menampilkan semua baris dari kedua tabel, mencocokkan yang bisa.
-- Catatan: MySQL tidak mendukung FULL JOIN secara langsung
-- Solusi: UNION dari LEFT JOIN dan RIGHT JOIN
SELECT s.nama, k.nama_kelas
FROM siswa s
LEFT JOIN kelas k ON s.kelas_id = k.id
UNION
SELECT s.nama, k.nama_kelas
FROM siswa s
RIGHT JOIN kelas k ON s.kelas_id = k.id
WHERE s.id IS NULL;
5. CROSS JOIN
Produk kartesian dari kedua tabel (setiap baris di tabel A dipasangkan dengan setiap baris di tabel B).
SELECT s.nama, k.nama_kelas
FROM siswa s
CROSS JOIN kelas k;
6. SELF JOIN
Join tabel dengan dirinya sendiri, biasanya untuk relasi hierarkis.
-- Contoh tabel karyawan dengan relasi atasan-bawahan
SELECT a.nama AS karyawan, b.nama AS atasan
FROM karyawan a
JOIN karyawan b ON a.atasan_id = b.id;
Multiple Joins
Kita bisa menggabungkan lebih dari dua tabel dalam satu query.
-- Menampilkan siswa, kelas, dan nilai mereka
SELECT s.nama AS siswa,
k.nama_kelas AS kelas,
mp.nama_mapel AS mata_pelajaran,
n.nilai
FROM nilai n
INNER JOIN siswa s ON n.siswa_id = s.id
INNER JOIN mata_pelajaran mp ON n.mapel_id = mp.id
LEFT JOIN kelas k ON s.kelas_id = k.id
ORDER BY s.nama, mp.nama_mapel;
Studi Kasus Lanjutan
1. Menghitung Rata-rata Nilai per Siswa
SELECT s.nama, AVG(n.nilai) AS rata_rata
FROM siswa s
LEFT JOIN nilai n ON s.id = n.siswa_id
GROUP BY s.id
ORDER BY rata_rata DESC;
2. Menampilkan Siswa dan Nilai di Mata Pelajaran Tertentu
SELECT s.nama, n.nilai
FROM siswa s
JOIN nilai n ON s.id = n.siswa_id
JOIN mata_pelajaran mp ON n.mapel_id = mp.id
WHERE mp.nama_mapel = 'Matematika'
ORDER BY n.nilai DESC;
3. Menemukan Siswa yang Tidak Mengambil Mata Pelajaran Tertentu
SELECT s.nama
FROM siswa s
WHERE s.id NOT IN (
SELECT n.siswa_id
FROM nilai n
JOIN mata_pelajaran mp ON n.mapel_id = mp.id
WHERE mp.nama_mapel = 'Fisika'
);