Perintah SQL part 9

1. Cross Join

Operasi tabel yang pertama adalah cross join atau disebut juga sebagai Cartesian join. Pada cross join, semua data dalam tabel yang pertama dipasangkan dengan semua data pada tabel yang kedua. Berikut adalah contohnya

SYNTAX DASAR

SELECT * FROM <nama_tabel1>, <nama_tabel2>;

mysql> select * from pegawai_join, jabatan;

mysql> select * from pegawai_join, jabatan;

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

| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji | id_jabatan | nama_jabatan |

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

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |1 | C.E.O |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |2 | Manager |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |3 | Kepala Unit |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |4 | Supervisor |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |5 | Staff Senior |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |6 | Staff Junior |

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |7 | Tenaga Kontrak |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |1 | C.E.O |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |2 | Manager |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |3 | Kepala Unit |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |4 | Supervisor |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |5 | Staff Senior |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |6 | Staff Junior |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |7 | Tenaga Kontrak |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |1 | C.E.O |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |2 | Manager |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |3 | Kepala Unit |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |4 | Supervisor |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |5 | Staff Senior |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |6 | Staff Junior |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |7 | Tenaga Kontrak |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |1 | C.E.O |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |2 | Manager |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |3 | Kepala Unit |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |4 | Supervisor |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |5 | Staff Senior |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |6 | Staff Junior |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |7 | Tenaga Kontrak |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |1 | C.E.O |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |2 | Manager |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |3 | Kepala Unit |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |4 | Supervisor |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |5 | Staff Senior |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |6 | Staff Junior |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |7 | Tenaga Kontrak |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |1 | C.E.O |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |2 | Manager |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |3 | Kepala Unit |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |4 | Supervisor |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |5 | Staff Senior |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |6 | Staff Junior |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |7 | Tenaga Kontrak |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |1 | C.E.O |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |2 | Manager |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |3 | Kepala Unit |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |4 | Supervisor |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |5 | Staff Senior |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |6 | Staff Junior |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |7 | Tenaga Kontrak |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |1 | C.E.O |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |2 | Manager |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |3 | Kepala Unit |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |4 | Supervisor |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |5 | Staff Senior |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |6 | Tenaga Kontrak |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |1 | C.E.O |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |2 | Manager |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |3 | Kepala Unit |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |4 | Supervisor |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |5 | Staff Senior |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |6 | Staff Junior |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |7 | Tenaga Kontrak |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |1 | C.E.O |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |2 | Manager |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |3 | Kepala Unit |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |4 | Supervisor |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |5 | Staff Senior |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |6 | Staff Junior |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |7 | Tenaga Kontrak |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |1 | C.E.O |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |2 | Manager |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |3 | Kepala Unit |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |4 | Supervisor |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |5 | Staff Senior |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |6 | Staff Junior |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |7 | Tenaga Kontrak |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |1 | C.E.O |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |2 | Manager |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |3 | Kepala Unit |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |4 | Supervisor |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |5 | Staff Senior |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |6 | Staff Junior |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |7 | Tenaga Kontrak |

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

105 rows in set (0.00 sec)

Pada Cros Join semua data di table pegawai akan direlasikan satu persatu dengan data di table jabatan.

Jadi ada 105 baris hasil cross join.

2. Equi-Join atau Inner Join

Inner join menggabungkan tabel dengan membandingkan nilai yang sama antara dua buah kolom. Kolom yang dibandingkan dapat kita spesifikasikan.

mysql> SELECT * FROM pegawai, jabatan WHERE pegawai.jabatan = jabatan. nama_jabatan;

mysql> SELECT * FROM pegawai, jabatan WHERE pegawai.jabatan=jabatan.nama_jabatan;

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

| nip | nama_peg | alamat_peg | jabatan | gaji | id_jabatan | nama_jabatan |

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

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | 1 | C.E.O |

| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | 2 | Manager |

| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |

| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |

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

15 rows in set (0.01 sec)

Selain Menggunakan WHERE kita juga bisa menggunakan INNER JOIN.. TO..

Berikut contoh syntaxnya :

mysql> SELECT * FROM pegawai INNER JOIN jabatan ON pegawai.jabatan = jabatan.nama_jabatan;

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

| nip | nama_peg | alamat_peg | jabatan | gaji | id_jabatan | nama_jabatan |

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

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | 1 | C.E.O |

| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | 2 | Manager |

| PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |

| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |

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

15 rows in set (0.00 sec)

Kita juga bisa menggunakan INNER JOIN.. USING.. sebagai perintah Join

Berikut contohnya :

mysql> SELECT * FROM pegawai_join INNER JOIN jabatan using (id_jabatan);

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

| id_jabatan | nip | nama_peg | alamat_peg | jabatan | gaji | nama_jabatan |

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

| 1 | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | C.E.O |

| 2 | PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | Manager |

| 3 | PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 | Kepala Unit |

| 3 | PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 | Kepala Unit |

| 4 | PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | Supervisor |

| 4 | PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | Supervisor |

| 5 | PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | Staff Senior |

| 5 | PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | Staff Senior |

| 5 | PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | Staff Senior |

| 6 | PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 | Staff Junior |

| 6 | PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 | Staff Junior |

| 6 | PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 | Staff Junior |

| 6 | PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 | Staff Junior |

| 7 | PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | Tenaga Kontrak |

| 7 | PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | Tenaga Kontrak |

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

15 rows in set (0.00 sec)

3. Natural Join

Natural join sebenarnya mirip dengan INNER JOIN. Namun kita tidak perlu menspesifikasikan kolom mana yang ingin kita bandingkan. Secara automatis, MySQL akan mencari kolom pada dua buah tabel yang memiliki nilai yang sama dan membandingkannya. Sebagai contoh, untuk tabel pegawai_join dan jabatan, yang dibandingkan adalah kolom id_jabatan yang ada di keduanya.

mysql> select * from pegawai_join NATURAL JOIN jabatan;

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

| id_jabatan | nip | nama_peg | alamat_peg | jabatan | gaji | nama_jabatan |

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

| 1 | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | C.E.O |

| 2 | PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | Manager |

| 3 | PEG-1003 | Olga Syahputra | Jakarta | Kepala Unit | 6000000 | Kepala Unit |

| 3 | PEG-1004 | Chelsea Olivia | Bandung | Kepala Unit | 6000000 | Kepala Unit |

| 4 | PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | Supervisor |

| 4 | PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | Supervisor |

| 5 | PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | Staff Senior |

| 5 | PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | Staff Senior |

| 5 | PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | Staff Senior |

| 6 | PEG-1010 | Rano Karno | Solo | Staff Junior | 2000000 | Staff Junior |

| 6 | PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff Junior | 2000000 | Staff Junior |

| 6 | PEG-1012 | Ilham Ungara | Jakarta | Staff Junior | 2000000 | Staff Junior |

| 6 | PEG-1013 | Endang Melati | Madiun | Staff Junior | 2000000 | Staff Junior |

| 7 | PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | Tenaga Kontrak |

| 7 | PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | Tenaga Kontrak |

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

15 rows in set (0.00 sec)

4. Left Join dan Right Join

Left join dan right join digunakan untuk menghindari data yang hilang karena mungkin ada data yang belum direferensi oleh tabel yang lain.

Sebagai contoh, kita tambahkan data di jabatan tapi kita tidak referensikan di tabel employee_join sebagai berikut:

mysql> insert into jabatan values (8,'Tenaga Magang');

Query OK, 1 row affected (0.05 sec)

Kemudian jika kita lakukan LEFT JOIN dengan tabel jobs yang kita sebut terlebih dahulu (atau di sebelah kiri) maka akan muncul sebagai berikut:

mysql> select * from jabatan t1 LEFT JOIN pegawai_join t2 ON t1.id_jabatan = t2. id_jabatan;

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

| id_jabatan | nama_jabatan | nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji |

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

| 1 | C.E.O | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |

| 2 | Manager | PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |

| 3 | Kepala Unit | PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 |

| 3 | Kepala Unit | PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 |

| 4 | Supervisor | PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |

| 4 | Supervisor | PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |

| 5 | Staff Senior | PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |

| 5 | Staff Senior | PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |

| 5 | Staff Senior | PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |

| 6 | Staff Junior | PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 |

| 6 | Staff Junior | PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 |

| 6 | Staff Junior | PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 |

| 6 | Staff Junior | PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 |

| 7 | Tenaga Kontrak | PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |

| 7 | Tenaga Kontrak | PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |

| 8 | Tenaga Magang | NULL | NULL | NULL | NULL | NULL | NULL |

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

16 rows in set (0.00 sec)

Meskipun pada tabel jabatan ada data yang belum direferensi, namun data tersebut tetap ditampilkan dan diberi nilai NULL untuk nilai di tabel pegawai_join yang belum diketahui. Hal yang sama juga berlaku untuk RIGHT JOIN, kali ini dengan tabel jabatan berada di sebelah kanan.

mysql> select * from pegawai_join t1 RIGHT JOIN jabatan t2 ON t1.id_jabatan = t2.id_jabatan;

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

| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji | id_jabatan | nama_jabatan |

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

| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 1 | C.E.O |

| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 2 | Manager |

| PEG-1003 | Olga Syahputra | Jakarta | 3 | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1004 | Chelsea Olivia | Bandung | 3 | Kepala Unit | 6000000 | 3 | Kepala Unit |

| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 4 | Supervisor |

| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 5 | Staff Senior |

| PEG-1010 | Rano Karno | Solo | 6 | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1013 | Endang Melati | Madiun | 6 | Staff Junior | 2000000 | 6 | Staff Junior |

| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |

| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |

| NULL | NULL | NULL | NULL | NULL | NULL | 8 | Tenaga Magang |

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

16 rows in set (0.02 sec)

5. Update menggunakan Join Table

Kita juga dapat melakukan update menggunakan Join table. Bentuk sintaksnya adalah

UPDATE <table1>, <table2> SET <column_name> = 'new_value' WHERE <conditions>

Misal, kita ingin merubah nama belakang dari employee yang pekerjaannya Spy menjadi 'Bono'. Kita dapat melakukannya sebagai berikut:

mysql> UPDATE pegawai_join, jabatan SET nama_peg = 'Soeharto Mangantelo' WHERE pegawai_join.id_jabatan = jabatan.id_jabatan AND jabatan.nama_jabatan = 'C.E.O';

Query OK, 1 row affected (0.85 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT nip, nama_peg, jabatan FROM pegawai_join INNER JOIN jabatan USING (id_jabatan) LIMIT 3;

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

| nip | nama_peg | jabatan |

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

| PEG-1001 | Soeharto Mangantelo | C.E.O |

| PEG-1002 | Felix Nababan | Manager |

| PEG-1003 | Olga Syahputra | Kepala Unit |

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

3 rows in set (0.00 sec)

6. Delete menggunakan join table

Kita juga dapat menghapus data menggunakan join table sebagai berikut:

mysql> DELETE pegawai_join FROM pegawai_join, jabatan WHERE pegawai_join.id_jabatan = jabatan.id_jabatan AND jabatan.nama_jabatan = 'Manager';

Query OK, 1 row affected (0.05 sec)

mysql> SELECT nip, nama_peg, jabatan FROM pegawai_join INNER JOIN jabatan USING (id_jabatan) LIMIT 3;

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

| nip | nama_peg | jabatan |

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

| PEG-1001 | Soeharto Mangantelo | C.E.O |

| PEG-1003 | Olga Syahputra | Kepala Unit |

| PEG-1004 | Chelsea Olivia | Kepala Unit |

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

3 rows in set (0.00 sec)

Tweet

Artikel keren lainnya:

Ditulis oleh kata-quotes pada tanggal Wednesday, September 2, 2015