SQL Join
Tables
Operasi join atau penggabungan “menyamping” antar table
adalah operasi dasar database relasional yang sangat penting. Untuk mendukung
perancangan database resional yang baik, berikut adalah penjelasan dan
ilustrasi mengenai join ini.
penulis masih menggunakan table : kelas, student, matakuliah,
krs
untuk contoh sederhana penulis mencoba menjoinkan table
kelas, dan student. yang diambil adalah nis, nama, kodekelas, nama kelas. (
bentuk umum )
select b.nis, b.nama, a.kode kodekelas, a.nama namakelas from
kelas a, student b where a.kode = b.kelas;
+———-+—————–+———–+———–+
| nis | nama | kodekelas | namakelas |
+———-+—————–+———–+———–+
| 20170002 | Angel Christina | K1 | Kelas 1 |
| 20170004 | Charles | K1 | Kelas 1 |
| 20170001 | Aldo Berton | K2 | Kelas 2 |
| 20170003 | Bella Lorenza | K2 | Kelas 2 |
| 20170005 | Davin | K4 | Kelas 4 |
| 20170006 | Donna | K4 | Kelas 4 |
| 20170007 | Dorothy | K5 | Kelas 5 |
| 20170009 | Evita | K5 | Kelas 5 |
| 20170008 | Dova Ariella | K6 | Kelas 6 |
| 20170010 | Grace Florence | K6 | Kelas 6 |
+———-+—————–+———–+———–+
| nis | nama | kodekelas | namakelas |
+———-+—————–+———–+———–+
| 20170002 | Angel Christina | K1 | Kelas 1 |
| 20170004 | Charles | K1 | Kelas 1 |
| 20170001 | Aldo Berton | K2 | Kelas 2 |
| 20170003 | Bella Lorenza | K2 | Kelas 2 |
| 20170005 | Davin | K4 | Kelas 4 |
| 20170006 | Donna | K4 | Kelas 4 |
| 20170007 | Dorothy | K5 | Kelas 5 |
| 20170009 | Evita | K5 | Kelas 5 |
| 20170008 | Dova Ariella | K6 | Kelas 6 |
| 20170010 | Grace Florence | K6 | Kelas 6 |
+———-+—————–+———–+———–+
Join table terdiri dari :
*. Inner Join
select b.nis, b.nama, a.kode kodekelas, a.nama namakelas
from kelas a inner join student b on a.kode = b.kelas;
from kelas a inner join student b on a.kode = b.kelas;
hasilnya sama dengan diatas.
*. Outer Join, terdiri dari : LEFT JOIN dan RIGHT
JOIN
select b.nis, b.nama, a.kode kodekelas, a.nama namakelas
from kelas a left join student b on a.kode = b.kelas;
from kelas a left join student b on a.kode = b.kelas;
+———-+—————–+———–+———–+
| nis | nama | kodekelas | namakelas |
+———-+—————–+———–+———–+
| 20170002 | Angel Christina | K1 | Kelas 1 |
| 20170004 | Charles | K1 | Kelas 1 |
| 20170001 | Aldo Berton | K2 | Kelas 2 |
| 20170003 | Bella Lorenza | K2 | Kelas 2 |
| NULL | NULL | K3 | Kelas 3 |
| 20170005 | Davin | K4 | Kelas 4 |
| 20170006 | Donna | K4 | Kelas 4 |
| 20170007 | Dorothy | K5 | Kelas 5 |
| 20170009 | Evita | K5 | Kelas 5 |
| 20170008 | Dova Ariella | K6 | Kelas 6 |
| 20170010 | Grace Florence | K6 | Kelas 6 |
+———-+—————–+———–+———–+
| nis | nama | kodekelas | namakelas |
+———-+—————–+———–+———–+
| 20170002 | Angel Christina | K1 | Kelas 1 |
| 20170004 | Charles | K1 | Kelas 1 |
| 20170001 | Aldo Berton | K2 | Kelas 2 |
| 20170003 | Bella Lorenza | K2 | Kelas 2 |
| NULL | NULL | K3 | Kelas 3 |
| 20170005 | Davin | K4 | Kelas 4 |
| 20170006 | Donna | K4 | Kelas 4 |
| 20170007 | Dorothy | K5 | Kelas 5 |
| 20170009 | Evita | K5 | Kelas 5 |
| 20170008 | Dova Ariella | K6 | Kelas 6 |
| 20170010 | Grace Florence | K6 | Kelas 6 |
+———-+—————–+———–+———–+
dari tampilan hasil keluar data null, karena kelas K3 tidak ada
pada student;
select b.nis, b.nama, a.kode kodekelas, a.nama namakelas
from kelas a right join student b on a.kode = b.kelas;
from kelas a right join student b on a.kode = b.kelas;
+———-+—————–+———–+———–+
| nis | nama | kodekelas | namakelas |
+———-+—————–+———–+———–+
| 20170001 | Aldo Berton | K2 | Kelas 2 |
| 20170002 | Angel Christina | K1 | Kelas 1 |
| 20170003 | Bella Lorenza | K2 | Kelas 2 |
| 20170004 | Charles | K1 | Kelas 1 |
| 20170005 | Davin | K4 | Kelas 4 |
| 20170006 | Donna | K4 | Kelas 4 |
| 20170007 | Dorothy | K5 | Kelas 5 |
| 20170008 | Dova Ariella | K6 | Kelas 6 |
| 20170009 | Evita | K5 | Kelas 5 |
| 20170010 | Grace Florence | K6 | Kelas 6 |
+———-+—————–+———–+———–+
| nis | nama | kodekelas | namakelas |
+———-+—————–+———–+———–+
| 20170001 | Aldo Berton | K2 | Kelas 2 |
| 20170002 | Angel Christina | K1 | Kelas 1 |
| 20170003 | Bella Lorenza | K2 | Kelas 2 |
| 20170004 | Charles | K1 | Kelas 1 |
| 20170005 | Davin | K4 | Kelas 4 |
| 20170006 | Donna | K4 | Kelas 4 |
| 20170007 | Dorothy | K5 | Kelas 5 |
| 20170008 | Dova Ariella | K6 | Kelas 6 |
| 20170009 | Evita | K5 | Kelas 5 |
| 20170010 | Grace Florence | K6 | Kelas 6 |
+———-+—————–+———–+———–+
catatan: yang dilihat letak table
·
penggabungan 3 table : matakuliah, student, krs, yang mau
ditampilkan adalah nis, nama, nama_makuliah, sks
select b.nis, b.nama, a.nama nama_matakuliah, a.sks from
matakuliah a, student b, krs c where a.kode = c.kode_matakuliah and b.nis =
c.nis;
+———-+—————–+—————–+——+
| nis | nama | nama_matakuliah | sks |
+———-+—————–+—————–+——+
| 20170001 | Aldo Berton | Fisika | 4 |
| 20170001 | Aldo Berton | Kalkulus | 4 |
| 20170001 | Aldo Berton | Pancasila | 2 |
| 20170001 | Aldo Berton | Kewarganegaraan | 2 |
| 20170001 | Aldo Berton | Akuntansi 1 | 3 |
| 20170002 | Angel Christina | Fisika | 4 |
| 20170002 | Angel Christina | Kalkulus | 4 |
| 20170002 | Angel Christina | Pancasila | 2 |
| 20170002 | Angel Christina | Kewarganegaraan | 2 |
| 20170002 | Angel Christina | Akuntansi 1 | 3 |
| 20170003 | Bella Lorenza | Fisika | 4 |
| 20170003 | Bella Lorenza | Kalkulus | 4 |
| 20170003 | Bella Lorenza | Pancasila | 2 |
| 20170003 | Bella Lorenza | Kewarganegaraan | 2 |
| 20170003 | Bella Lorenza | Akuntansi 1 | 3 |
| 20170003 | Bella Lorenza | Akuntansi 2 | 3 |
+———-+—————–+—————–+——+
| nis | nama | nama_matakuliah | sks |
+———-+—————–+—————–+——+
| 20170001 | Aldo Berton | Fisika | 4 |
| 20170001 | Aldo Berton | Kalkulus | 4 |
| 20170001 | Aldo Berton | Pancasila | 2 |
| 20170001 | Aldo Berton | Kewarganegaraan | 2 |
| 20170001 | Aldo Berton | Akuntansi 1 | 3 |
| 20170002 | Angel Christina | Fisika | 4 |
| 20170002 | Angel Christina | Kalkulus | 4 |
| 20170002 | Angel Christina | Pancasila | 2 |
| 20170002 | Angel Christina | Kewarganegaraan | 2 |
| 20170002 | Angel Christina | Akuntansi 1 | 3 |
| 20170003 | Bella Lorenza | Fisika | 4 |
| 20170003 | Bella Lorenza | Kalkulus | 4 |
| 20170003 | Bella Lorenza | Pancasila | 2 |
| 20170003 | Bella Lorenza | Kewarganegaraan | 2 |
| 20170003 | Bella Lorenza | Akuntansi 1 | 3 |
| 20170003 | Bella Lorenza | Akuntansi 2 | 3 |
+———-+—————–+—————–+——+
*. untuk melihat total sks per student :
select b.nis, b.nama, sum(a.sks) jumlah_sks from matakuliah a,
student b, krs c where a.kode = c.kode_matakuliah and b.nis = c.nis group by
b.nis, b.nama;
+———-+—————–+————+
| nis | nama | jumlah_sks |
+———-+—————–+————+
| 20170001 | Aldo Berton | 15 |
| 20170002 | Angel Christina | 15 |
| 20170003 | Bella Lorenza | 18 |
+———-+—————–+————+
| nis | nama | jumlah_sks |
+———-+—————–+————+
| 20170001 | Aldo Berton | 15 |
| 20170002 | Angel Christina | 15 |
| 20170003 | Bella Lorenza | 18 |
+———-+—————–+————+
*. cara untuk melihat jumlah student per matakuliah
select a.kode kode_matakuliah, a.nama nama_matakuliah,
count(b.nis) jumlah_student from matakuliah a, student b, krs c where a.kode =
c.kode_matakuliah and b.nis = c.nis group by a.kode, a.nama;
+—————–+—————–+—————-+
| kode_matakuliah | nama_matakuliah | jumlah_student |
+—————–+—————–+—————-+
| MTK0001 | Fisika | 3 |
| MTK0002 | Kalkulus | 3 |
| MTK0003 | Pancasila | 3 |
| MTK0004 | Kewarganegaraan | 3 |
| MTK0005 | Akuntansi 1 | 3 |
| MTK0006 | Akuntansi 2 | 1 |
+—————–+—————–+—————-+
| kode_matakuliah | nama_matakuliah | jumlah_student |
+—————–+—————–+—————-+
| MTK0001 | Fisika | 3 |
| MTK0002 | Kalkulus | 3 |
| MTK0003 | Pancasila | 3 |
| MTK0004 | Kewarganegaraan | 3 |
| MTK0005 | Akuntansi 1 | 3 |
| MTK0006 | Akuntansi 2 | 1 |
+—————–+—————–+—————-+
catatan :
untuk menampilkan data tertentu gunakan kondisi seperti yang di
tutorial SQL Simple.