Table Join
1.
Cross
Join
2.
Equi-Join
atau Inner Join
3.
Natural
Join
Table Join
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)
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)
0 komentar:
Posting Komentar