Praktikum Table Join pada MySQL

Dalam basis data relasional, kita mengenal relasi antar tabel. Untuk melakukan query terhadap dua atau lebih tabel yang memiliki relasi, kita bisa menggunakan fitur table join di MySQL. Sebelum kita mulai mempelajari Table Join kita persiapkan dulu Tabel-tabel pendukungnya :

mysql> desc jabatan;

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| id_jabatan | int(2)      | NO   | PRI | NULL    |       |

| nm_jabatan | varchar(20) | YES  |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.22 sec)

mysql> select * from jabatan;

+------------+----------------+

| id_jabatan | nm_jabatan     |

+------------+----------------+

|          1 | C.E.O          |

|          2 | Manager        |

|          3 | kepala Unit    |

|          4 | Supervisor     |

|          5 | Staff Senior   |

|          6 | Staff Junior   |

|          7 | Tenaga Kontrak |

+------------+----------------+

7 rows in set (0.00 sec)

Setelah kita punya table jabatan / job, kita buat tabel baru untuk menerapkan join, kita copykan table pegawai ke tabel pegawai_join

mysql> create table pegawai_join AS (select*from pegawai);
Query OK, 15 rows affected (0.45 sec)
Records: 15  Duplicates: 0  Warnings: 0
mysql> select * from pegawai_join;
+----------+-----------------------+-------------+----------------+---------+
| nip      | nama_peg              | alamat_peg  | jabatan        | gaji    |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta  | C.E.O          | 9000000 |
| PEG-1002 | Felix Nababan         | Medan       | Manager        | 8000000 |
| PEG-1003 | Olga Syahputra        | Jakarta     | Kepala Unit    | 6000000 |
| PEG-1004 | Chelsea Olivia        | Bandung     | Kepala Unit    | 6000000 |
| PEG-1005 | Tuti Wardani          | Jawa Tengah | Supervisor     | 4500000 |
| PEG-1006 | Budi Drajat           | Malang      | Supervisor     | 4500000 |
| PEG-1007 | Bambang Pamungkas     | Kudus       | Staff Senior   | 3000000 |
| PEG-1008 | Ely Oktafiani         | Yogyakarta  | Staff Senior   | 3000000 |
| PEG-1009 | Rani Wijaya           | Magelang    | Staff Senior   | 3000000 |
| PEG-1010 | Rano Karno            | Solo        | Staff Junior   | 2000000 |
| PEG-1011 | Rahmadi Sholeh        | Yogyakarta  | Staff Junior   | 2000000 |
| PEG-1012 | Ilham Ungara          | Jakarta     | Staff Junior   | 2000000 |
| PEG-1013 | Endang Melati         | Madiun      | Staff Junior   | 2000000 |
| PEG-1014 | Donny Damara          | Makasar     | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem                | Yogyakarta  | Tenaga Kontrak |  500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)

Untuk merelasikan table pegawai_join dan table jabatan kita tambahkan kolom id_jabatan pada table pegawai_join untuk mereferensi ke dua table.

mysql> alter table pegawai_join ADD id_jabatan int(2) AFTER alamat_peg;
Query OK, 15 rows affected (0.20 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> desc pegawai_join;

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| nip        | varchar(8)  | NO   |     | NULL    |       |

| nama_peg   | varchar(50) | YES  |     | NULL    |       |

| alamat_peg | varchar(50) | YES  |     | NULL    |       |

| id_jabatan | int(2)      | YES  |     | NULL    |       |

| jabatan    | varchar(20) | YES  |     | NULL    |       |

| gaji       | int(7)      | YES  |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

6 rows in set (0.00 sec)

Jika kita lihat isi tablenya kolom id_jabatan masih NULL atau kosong karena ke dua table belum berelasi.
mysql> select * from pegawai_join;
+----------+-----------------------+-------------+------------+----------------+---------+
| nip      | nama_peg              | alamat_peg  | id_jabatan | jabatan        | gaji    |
+----------+-----------------------+-------------+------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta  |       NULL | C.E.O          | 9000000 |
| PEG-1002 | Felix Nababan         | Medan       |       NULL | Manager        | 8000000 |
| PEG-1003 | Olga Syahputra        | Jakarta     |       NULL | Kepala Unit    | 6000000 |
| PEG-1004 | Chelsea Olivia        | Bandung     |       NULL | Kepala Unit    | 6000000 |
| PEG-1005 | Tuti Wardani          | Jawa Tengah |       NULL | Supervisor     | 4500000 |
| PEG-1006 | Budi Drajat           | Malang      |       NULL | Supervisor     | 4500000 |
| PEG-1007 | Bambang Pamungkas     | Kudus       |       NULL | Staff Senior   | 3000000 |
| PEG-1008 | Ely Oktafiani         | Yogyakarta  |       NULL | Staff Senior   | 3000000 |
| PEG-1009 | Rani Wijaya           | Magelang    |       NULL | Staff Senior   | 3000000 |
| PEG-1010 | Rano Karno            | Solo        |       NULL | Staff Junior   | 2000000 |
| PEG-1011 | Rahmadi Sholeh        | Yogyakarta  |       NULL | Staff Junior   | 2000000 |
| PEG-1012 | Ilham Ungara          | Jakarta     |       NULL | Staff Junior   | 2000000 |
| PEG-1013 | Endang Melati         | Madiun      |       NULL | Staff Junior   | 2000000 |
| PEG-1014 | Donny Damara          | Makasar     |       NULL | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem                | Yogyakarta  |       NULL | Tenaga Kontrak |  500000 |
+----------+-----------------------+-------------+------------+----------------+---------+
15 rows in set (0.00 sec)
Agar data di kolom bisa masuk maka kita relasikan sbb:
Kita gunakan UPDATE untuk mengubah diskripsi dari kolom id_jabatan di table pegawai.

UPDATE <nama_table1> , <nama_table2> SET <nama_table1.nama_kolom> = <nama_table1. nama_kolom2> WHERE //data yang sama// <nama_table1.nama_kolom1> = <nama_table2.nama_kolom2>

mysql> UPDATE pegawai_join, jabatan SET pegawai_join.id_jabatan = jabatan.id_jabatan WHERE pegawai_join.jabatan=jabatan.nm_jabatan;

Query OK, 15 rows affected (0.48 sec)
Rows matched: 15  Changed: 15  Warnings: 0

mysql> select * from pegawai_join;
+----------+-----------------------+-------------+------------+----------------+---------+
| nip      | nama_peg              | alamat_peg  | id_jabatan | jabatan        | gaji    |
+----------+-----------------------+-------------+------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta  |          1 | C.E.O          | 9000000 |
| PEG-1002 | Felix Nababan         | Medan       |          2 | Manager        | 8000000 |
| PEG-1003 | Olga Syahputra        | Jakarta     |          3 | Kepala Unit    | 6000000 |
| PEG-1004 | Chelsea Olivia        | Bandung     |          3 | Kepala Unit    | 6000000 |
| PEG-1005 | Tuti Wardani          | Jawa Tengah |          4 | Supervisor     | 4500000 |
| PEG-1006 | Budi Drajat           | Malang      |          4 | Supervisor     | 4500000 |
| PEG-1007 | Bambang Pamungkas     | Kudus       |          5 | Staff Senior   | 3000000 |
| PEG-1008 | Ely Oktafiani         | Yogyakarta  |          5 | Staff Senior   | 3000000 |
| PEG-1009 | Rani Wijaya           | Magelang    |          5 | Staff Senior   | 3000000 |
| PEG-1010 | Rano Karno            | Solo        |          6 | Staff Junior   | 2000000 |
| PEG-1011 | Rahmadi Sholeh        | Yogyakarta  |          6 | Staff Junior   | 2000000 |
| PEG-1012 | Ilham Ungara          | Jakarta     |          6 | Staff Junior   | 2000000 |
| PEG-1013 | Endang Melati         | Madiun      |          6 | Staff Junior   | 2000000 |
| PEG-1014 | Donny Damara          | Makasar     |          7 | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem                | Yogyakarta  |          7 | Tenaga Kontrak |  500000 |
+----------+-----------------------+-------------+------------+----------------+---------+
15 rows in set (0.00 sec)