efi pantu: Q U E R Y

MATERI PART 3 – Prak. BASISDATA

Q U E R Y

LABORATORIUM 4 – BASISDATA

MENU MINGGU INI

I. SELECT Statement

1. Mengambil data dengan SELECT

2. Query menggunakan parameter kondisi WHERE

3. Query menggunakan beberapa parameter kondisional

4. Memberikan alias hasil query pada SELECT

5. Query data bertipe teks dengan pattern matching

6. Query data unik menggunakan DISTINCT

7. Membatasi hasil query dengan LIMIT

8. Mengelompokkan hasil query menggunakan GROUP BY

9. Mendapatkan jumlah anggota setiap kelompok menggunakan COUNT()

10. Parameter kondisional dengan HAVING

11. Mengurutkan hasil query menggunakan ORDER BY

12. Mengurutkan hasil query berdasarkan lebih dari satu kolom

13. Kombinasi ORDER BY dengan LIMIT

14. Operator BETWEEN

15. MAX, MIN, AVERAGE data dari tabel

II. SUB QUERY

1. Sub query dengan ALL

2. Sub query dengan ANY

3. Sub query dengan EXISTS

4. Sub query dengan IN

III. Table Join

1. Cross Join

2. Equi-Join atau Inner Join

3. Natural Join

4. Left Join dan Right Join

5. Update menggunakan Join Table

6. Delete menggunakan join table

SELECT Statement

Sebelum kita memulai pembahsan dengan SELECT terlebih dahulu kita siapkan tabel baru bernama table pegawai.

mysql> create table pegawai(nip varchar(8) primary key, nama_peg varchar(50), al

amat_peg varchar(50), jabatan varchar(20), gaji int(7));

Query OK, 0 rows affected (0.70 sec)

mysql> desc pegawai;

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

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

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

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

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

5 rows in set (0.13 sec)

Lalu kita isikan dengan data yang dudah tersedia

mysql> load data local infile 'G://LAB4/BASISDATA/file.txt' into table pegawai;

Query OK, 15 rows affected (0.63 sec)

Records: 15 Deleted: 0 Skipped: 0 Warnings: 0

1. Mengambil Data dengan SELECT

Perintah select untuk mengambil data sudah sering kita pakai sebelumnya, untuk mengambil seluruh data yang ada di dalam suatu tabel kita bisa menggunakan perintah SELECT * FROM <nama_tabel>

CONTOH :

mysql> select * from pegawai;

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

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

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

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

15 rows in set (0.00 sec)

Perintah diatas menunjukan seluruh dara dari table pegawai

Kita juga bisa mengambil seluruh data tapi hanya kolom – kolom tertentu dengan perintah dasar : SELECT <NAMA_KOLOM, NAMA_KOLOM > FROM <NAMA_TABEL>

CONTOH :

mysql> select nip, nama_peg from pegawai;

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

| nip | nama_peg |

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

| PEG-1001 | Soeharto Mangundirejo |

| PEG-1002 | Felix Nababan |

| PEG-1003 | Olga Syahputra |

| PEG-1004 | Chelsea Olivia |

| PEG-1005 | Tuti Wardani |

| PEG-1006 | Budi Drajat |

| PEG-1007 | Bambang Pamungkas |

| PEG-1008 | Ely Oktafiani |

| PEG-1009 | Rani Wijaya |

| PEG-1010 | Rano Karno |

| PEG-1011 | Rahmadi Sholeh |

| PEG-1012 | Ilham Ungara |

| PEG-1013 | Endang Melati |

| PEG-1014 | Donny Damara |

| PEG-1015 | Paijem |

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

15 rows in set (0.00 sec)

2. Query menggunakan parameter kondisi WHERE

Statement WHERE disini akan digunakan untuk memfilter atau mengatur kolom dan baris mana yang akan kita munculkan atau kita ambil.

PERINTAH DASAR :

SELECT * FROM <nama_table> WHERE <kondisi>

CONTOH :

mysql> select * from pegawai where jabatan='Staff Junior';

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

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

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

4 rows in set (0.44 sec)

Perintah diatas menunjukkan data pegawai yang difilter berdasarkan jabatan yg berisi staff junior

mysql> select nip, nama_peg from pegawai where jabatan='Staff Junior';

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

| nip | nama_peg |

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

| PEG-1010 | Rano Karno |

| PEG-1011 | Rahmadi Sholeh |

| PEG-1012 | Ilham Ungara |

| PEG-1013 | Endang Melati |

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

4 rows in set (0.00 sec)

Untuk menampilkan tidak semua data atau hanya data pada kolom – kolom tertentu yg telah dituliskan dan tetap berdasarkan jabatan = junior staff

3. Query menggunakan beberapa parameter kondisional

Perintah yang digunakan disini menggunakan WHERE tapi yang membedakan perintah yang digunakan lebih menunjukkan kondisi yang diinginkan, dengan bisa menggunakan penggabungan beberapa parameter untuk menampilkan data. Penggabungan ini bisa menggunakan perintah AND atau OR, untuk lebih jelasnya bisa dilihat contoh :

mysql> select * from pegawai where alamat_peg ='Yogyakarta' AND gaji < 4000000;

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

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

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

3 rows in set (0.00 sec)

Perintah diatas menunjukkan pemfilteran dengan 2 kondisi yaitu berdasarkan alamat_peg dan gaji yang kurang dari 4jt

mysql> select nip, nama_peg from pegawai where alamat_peg ='Yogyakarta' OR alamat_peg ='Jakarta';

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

| nip | nama_peg |

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

| PEG-1001 | Soeharto Mangundirejo |

| PEG-1003 | Olga Syahputra |

| PEG-1008 | Ely Oktafiani |

| PEG-1011 | Rahmadi Sholeh |

| PEG-1012 | Ilham Ungara |

| PEG-1015 | Paijem |

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

6 rows in set (0.00 sec)

Jika perintah diatas menunjukkan data yang mempunyai alamat di Yogyakarta atau di Jakarta

4. Memberikan alias hasil query pada SELECT

Kita bisa menggunakan perintah SELECT untuk membuat alias pada suatu kolom dengan perintah AS.

mysql> select nama_peg AS nama_pegawai, alamat_peg AS asal from pegawai where alamat_peg='Jakarta';

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

| nama_pegawai | asal |

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

| Olga Syahputra | Jakarta |

| Ilham Ungara | Jakarta |

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

2 rows in set (0.00 sec)

Di sini pada saat menampilkan hasil, nama kolom akan diganti dengan perintah AS

5. Query data bertipe teks dengan pattern matching

Menampilkan data dengan mencocokan (matching) dengan kondisi yang kita inginkan.

Dengan menggunakan perintah ‘LIKE’ untuk perintah ‘seperti’ dan ‘NOT LIKE’ untuk perintah ‘tidak seperti’ dan beberapa simbol matching yg ada di SQL yaitu ‘_’ dan ‘%’ untuk lebih jelasnya bisa lihat di contoh :

mysql> select nama_peg from pegawai where jabatan like 'M______';

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

| nama_peg |

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

| Felix Nababan |

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

1 row in set (0.00 sec)

Pada perintah diatas menunjukkan bahwa data yang diminta adalah data yang memuat Jabatan berawalan M dan berjumlag 7 huruf.

mysql> select nama_peg from pegawai where jabatan like 'M%';

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

| nama_peg |

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

| Felix Nababan |

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

1 row in set (0.00 sec)

Perintah di atas menunjukkan data yang diminta adalah data nama pegawai yang jabatannya berawalan huruf ‘M’

mysql> select nama_peg from pegawai where alamat_peg like '%A';

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

| nama_peg |

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

| Soeharto Mangundirejo |

| Olga Syahputra |

| Ely Oktafiani |

| Rahmadi Sholeh |

| Ilham Ungara |

| Paijem |

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

6 rows in set (0.00 sec)

Perintah di atas menunjukkan data yang diminta adalah data nama pegawai yang alamatnya berakhiran huruf ‘A’

6. Query data unik menggunakan DISTINCT

Perintah DISTINCT digunakan untuk menampilkan data yang unik, jadi jika ada data yang sama tidak akan ditampilkan.

mysql> select alamat_peg from pegawai;

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

| alamat_peg |

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

| Yogyakarta |

| Medan |

| Jakarta |

| Bandung |

| Jawa Tengah |

| Malang |

| Kudus |

| Yogyakarta |

| Magelang |

| Solo |

| Yogyakarta |

| Jakarta |

| Madiun |

| Makasar |

| Yogyakarta |

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

15 rows in set (0.00 sec)

mysql> select distinct alamat_peg from pegawai;

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

| alamat_peg |

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

| Yogyakarta |

| Medan |

| Jakarta |

| Bandung |

| Jawa Tengah |

| Malang |

| Kudus |

| Magelang |

| Solo |

| Madiun |

| Makasar |

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

11 rows in set (0.05 sec)

7. Membatasi hasil query dengan LIMIT

Perintah LIMIT ini akan membatasi jumlah data yang akan kita tampilkan.

mysql> select * from pegawai LIMIT 5;

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

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

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

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

5 rows in set (0.00 sec)

8. Mengelompokkan hasil query menggunakan GROUP BY

Penampilan data dikelompokkan dengan GROUP BY, data yang ditampilkan adalah dta pertama dari kolom yang ditunjuk sebagai parameter GROP BY.

CONTOH :

mysql> select * from pegawai GROUP BY gaji;

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

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

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

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

8 rows in set (0.41 sec)

Menunjukkan pengngelompokan data berdasarkan gaji, jika ada yang memiliki gaji yang sama maka yang ditampilkan adalah data yang pertama, contoh Rano Karno dan Rahmadi Sholeh sama – sama memiliki gaji 2juta tetapi karena Rano Karno ada diatas Rahmadi maka yang ditampilkan adalah data dari Rano Karno.

9. Mendapatkan jumlah anggota setiap kelompok menggunakan COUNT()

Perintah COUNT() ini berkaitan dengan perintah GROUP BY, yaitu untuk mengetahui jumlah dari anggota GROUP, untuk lebih jelasnya bisa melihat contoh :

mysql> select gaji, count(*) FROM pegawai GROUP BY gaji;

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

| gaji | count(*) |

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

| 500000 | 1 |

| 1000000 | 1 |

| 2000000 | 4 |

| 3000000 | 3 |

| 4500000 | 2 |

| 6000000 | 2 |

| 8000000 | 1 |

| 9000000 | 1 |

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

8 rows in set (0.44 sec)

10.Parameter kondisional dengan HAVING

Penggunaan HAVING sangat mirip dengan penggunaan WHERE contoh :

mysql> select * from pegawai HAVING gaji > 6000000;

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

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

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

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

2 rows in set (0.00 sec)

Dari contoh diatas bisa terlihat jelas penggunaan HAVING dan WHERE sangat mirip

11.Mengurutkan hasil query menggunakan ORDER BY

Perintah ORDER BY adalah perintah untuk mengurutkan tabel secara ASC atau DESC,

CONTOH :

mysql> select * from pegawai ORDER BY nama_peg;

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

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

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

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

15 rows in set (0.42 sec)

Jika dilihat dari perintah diatas menunjukkan bahwa perintah ORDER BY akan diurutkan secara ASC secara default.

Untuk perintah ORDER BY secara DESC maka perintahnya sbb :

mysql> select * from pegawai ORDER BY nama_peg DESC;

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

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

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

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

15 rows in set (0.00 sec)

12.Mengurutkan hasil query berdasarkan lebih dari satu kolom

Pada perintah ini pengurutan data dilakukan tidak hanya berdasar 1 kolom, tetapi berdasar 2 kolom atau lebihpun bisa.

Agar data bisa terlihat jelas maka coba buat tabel pegawai2 dengan rincian sbb :

mysql> create table pegawai2(nama varchar(20), alamat varchar(20));

Query OK, 0 rows affected (0.47 sec)

mysql> desc pegawai2;

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

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

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

2 rows in set (0.11 sec)

mysql> select*from pegawai2;

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

| nama | alamat |

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

| Zeze | Yogyakarta |

| Zeze | Jakarta |

| Riza | Bandung |

| Riza | Aceh |

| Amir | Demak |

| Amir | Cilacap |

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

6 rows in set (0.00 sec)

Kita sudah punya tabel baru yang menunjukkan data pada kolom nama ada yang sama, tapi di kolom alamat berbeda maka bisa diurutkan sbb :

mysql> select nama, alamat from pegawai2 ORDER BY nama, alamat;

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

| nama | alamat |

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

| Amir | Cilacap |

| Amir | Demak |

| Riza | Aceh |

| Riza | Bandung |

| Zeze | Jakarta |

| Zeze | Yogyakarta |

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

6 rows in set (0.00 sec)

mysql> select * from pegawai2 ORDER BY nama, alamat desc;

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

| nama | alamat |

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

| Amir | Demak |

| Amir | Cilacap |

| Riza | Bandung |

| Riza | Aceh |

| Zeze | Yogyakarta |

| Zeze | Jakarta |

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

6 rows in set (0.00 sec)

Pada tabel ini telah diurutkan nama terlebih dahulu seteleh itu baru mengurutkan berdasarkan alamat.

13.Kombinasi ORDER BY dengan LIMIT

Perintah ORDER BY juga bisa dikombinasikan dengan perintah LIMIT, contohnya

mysql> select * from pegawai ORDER BY gaji LIMIT 5;

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

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

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

5 rows in set (0.00 sec)

Contoh diatas menunjukkan 5 gaji terendah di table pegawai.

14.Operator BETWEEN

Perintah BETWEEN digunakan untuk memfilter data diantara dua nilai yang dispesifikkan

mysql> select * from pegawai WHERE gaji BETWEEN 4000000 AND 9000000;

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

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

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

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

6 rows in set (0.00 sec)

Perintah diatas menunjukkan penampilan gaji diantara 4jt dan 9jt .

Jika perintah BETWEEN dilakukan untuk mencara data dalam range, ada juga NOT BETWEEN untuk mencarai data diluar range yang kita tentukan.

CATATAN : penginputan range diwalai dengan urutan yang lebih kecil, contoh 4jt – 9jt, atau Ely O – Olga S

mysql> select nama_peg, gaji from pegawai WHERE nama_peg NOT BETWEEN "Ely Oktafi

ani" AND "Olga Syahputra";

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

| nama_peg | gaji |

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

| Soeharto Mangundirejo | 9000000 |

| Chelsea Olivia | 6000000 |

| Tuti Wardani | 4500000 |

| Budi Drajat | 4500000 |

| Bambang Pamungkas | 3000000 |

| Rani Wijaya | 3000000 |

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

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

10 rows in set (0.00 sec)

Menunjukkan hasil dari NOT BETWEEN dalam menampilkan data yang bukan antara Ely Oktafiani dan Olga Syahputra

15.MAX, MIN, AVERAGE data dari tabel

Perintah untuk menunjukkan nilai maksimal atau minimal ataupun nilai rata – rata juga bisa kita lakukan di MySQL, dengan perintah SELECT diikuti oleh MIN, MAX, atau AVERAGE, untuk lebih jelasnya kita lihat contoh :

Mencari nilai MINIMAL è MIN

mysql> select MIN(gaji) from pegawai;

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

| MIN(gaji) |

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

| 500000 |

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

1 row in set (1.05 sec)

Mencari nilai MAKSIMAL è MAX

mysql> select MAX(gaji) from pegawai;

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

| MAX(gaji) |

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

| 9000000 |

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

1 row in set (0.00 sec)

Mencari nilai RATA - RATA è AVG

mysql> select AVG(gaji) from pegawai;

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

| AVG(gaji) |

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

| 3766666.6667 |

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

1 row in set (0.41 sec)

Menghitung Nilai Total è SUM

mysql> select SUM(gaji) from pegawai;

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

| SUM(gaji) |

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

| 56500000 |

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

1 row in set (0.39 sec)

SUB QUERY

Sub Query adalah penggunaan statement SELECT di dalam statement SELECT, Sub Query dideklarasikan di dalam perintah my qsl di dalam tanda (), dan biasa menggunakan salah satu dari statemen SELECT, UPDATE, SET, DELETE, atau DO

CONTOH :

mysql> select nama_peg, gaji from pegawai where gaji = (select max(gaji) from pe

gawai);

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

| nama_peg | gaji |

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

| Soeharto Mangundirejo | 9000000 |

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

1 row in set (0.06 sec)

Perintah ini hanya bisa menerima satu(1) buah hasil dari sub query, jika hasil dari sub query ada lebih adari satu maka akan terjadi error.

mysql> select nama_peg, gaji from pegawai where alamat_peg = (select alamat_peg

from pegawai where alamat_peg LIKE 'J%');

ERROR 1242 (21000): Subquery returns more than 1 row

Perintah diatas ini ERROR karena hasil sub query diatas ada lebih dari satu(1)

1. Sub query dengan ALL

Command ALL diikuti dengan operator perbandingan digunakan memiliki arti menampilkan nilai jika perbandingan bernilai benar untuk semua data. Berikut adalah contoh penggunaannya.

mysql> select nama_peg, gaji from pegawai where gaji < ALL(select gaji FROM pega

wai WHERE gaji > '2500000');

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

| nama_peg | gaji |

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

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Ilham Ungara | 2000000 |

| Endang Melati | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

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

6 rows in set (0.00 sec)

Contoh diatas menunjukkan penampilan atas nama pegawai beserta gajinya yang memiliki gaji kurang dari 2,5jt

2. Sub query dengan ANY

Command ANY diikuti dengan operator perbandingan memiliki arti menampilkan nilai yang sesuai dengan apapun yang dihasilkan oleh sub query. Alias dari ANY adalah SOME. Berikut adalah contohnya:

mysql> select nama_peg, gaji from pegawai where gaji < ANY(select gaji FROM pega

wai WHERE gaji > '2000000');

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

| nama_peg | gaji |

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

| Felix Nababan | 8000000 |

| Olga Syahputra | 6000000 |

| Chelsea Olivia | 6000000 |

| Tuti Wardani | 4500000 |

| Budi Drajat | 4500000 |

| Bambang Pamungkas | 3000000 |

| Ely Oktafiani | 3000000 |

| Rani Wijaya | 3000000 |

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Ilham Ungara | 2000000 |

| Endang Melati | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

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

14 rows in set (0.00 sec)

Pada perintah diatas kita perhatikan perintah select yang berada di dalam kurung (Sub Query), pada perintah itu menjelaskan bahwa penampilan daftar nama pegawai yang gajinya lebih dari 2jt, sedangkan Quert seutuhnya menampilkan nama pegawai yang gajinya lebih kecil daripada hasil sub query, Soeharto Mangundirejo tidak keluar di hasil karena tidak ada yang mempunyai gaji sebesar dirinya.

mysql> select nama_peg, gaji from pegawai where gaji < SOME(select gaji FROM peg

awai WHERE gaji > '2000000');

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

| nama_peg | gaji |

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

| Felix Nababan | 8000000 |

| Olga Syahputra | 6000000 |

| Chelsea Olivia | 6000000 |

| Tuti Wardani | 4500000 |

| Budi Drajat | 4500000 |

| Bambang Pamungkas | 3000000 |

| Ely Oktafiani | 3000000 |

| Rani Wijaya | 3000000 |

| Rano Karno | 2000000 |

| Rahmadi Sholeh | 2000000 |

| Ilham Ungara | 2000000 |

| Endang Melati | 2000000 |

| Donny Damara | 1000000 |

| Paijem | 500000 |

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

14 rows in set (0.45 sec)

3. Sub query dengan EXISTS

Perintah EXISTS disini berguna untuk mengartur penampilan hasil query, Query Utama akan dijalankan jika Sub Query bernilai TRUE (ada hasilnya) jika hasilnya kosong maka Query utama tidak akan dijalankan. Lawan dari statement EXISTS adalah NOT EXISTS

mysql> select * from pegawai where EXISTS (select * from pegawai where alamat_pe

g = 'Yogyakarta');

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

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

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

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

15 rows in set (0.00 sec)

Dari perintah diatas menunjukkan bahwa Query utama dieksesusi karena Sub Query bernilai TRUE atau ada hasilnya

mysql> select * from pegawai where EXISTS (select * from pegawai where alamat_pe

g = 'Kebumen');

Empty set (0.00 sec)

Sedangkan perintah ini tidak dijalankan karena Sub Query bernilai FALSE atau tidak ada hasilnya.

Jika kita ganti EXISTS dengan NOT EXISTS maka hasil akan keluar seperti di bawah ini, karena memang alamat-peg tidak ada yang bernilai ‘Kebumen’

mysql> select * from pegawai where NOT EXISTS (select * from pegawai where alama

t_peg = 'Kebumen');

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

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

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

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

15 rows in set (0.00 sec)

4. Sub query dengan IN

Jika operator ‘=’ hanya digunakan untuk hasil yang tepat satu, maka jika ingin menampilkan yang memiliki hasil lebih dari satu maka menggunakan perintah IN, berikut contohnya :

Kita buat terlebih dahulu sebuah tabel yang menunjukkan hasil yang kita inginkan, contoh tabel Job ini digunakan untuk menampilkan data yang hasil dari sub query lebih dari satu.

mysql> create table job( id int(2) primary key, job varchar(20));

Query OK, 0 rows affected (0.52 sec)

mysql> desc job;

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

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

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

| job | varchar(20) | YES | | NULL | |

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

2 rows in set (0.17 sec)

mysql> select*from job;

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

| id | job |

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

| 1 | Supervisor |

| 2 | Staff Senior |

| 3 | Staff Junior |

| 4 | Tenaga Kontrak |

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

4 rows in set (0.00 sec)

Setelah kita buat table job, maka kita bisa terapkan perintah IN sbb :

mysql> select nip, nama_peg, jabatan from pegawai where jabatan IN (select job f

rom job);

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

| nip | nama_peg | jabatan |

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

| PEG-1005 | Tuti Wardani | Supervisor |

| PEG-1006 | Budi Drajat | Supervisor |

| PEG-1007 | Bambang Pamungkas | Staff Senior |

| PEG-1008 | Ely Oktafiani | Staff Senior |

| PEG-1009 | Rani Wijaya | Staff Senior |

| PEG-1010 | Rano Karno | Staff Junior |

| PEG-1011 | Rahmadi Sholeh | Staff Junior |

| PEG-1012 | Ilham Ungara | Staff Junior |

| PEG-1013 | Endang Melati | Staff Junior |

| PEG-1014 | Donny Damara | Tenaga Kontrak |

| PEG-1015 | Paijem | Tenaga Kontrak |

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

11 rows in set (0.00 sec)

Terlihat di dalam hasil bahwa, data pegawai yang jabatannya ada di dalam tabel job akan ditampilkan, sedangkan yang tidak ada tidak akan ditampilkan.

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;

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

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

| id_jabatan | int(2) | NO | PRI | 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;

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

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

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

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

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;

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

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

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

| id_jabatan | int(2) | 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;

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

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

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

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

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;

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

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

| 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;

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

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

| 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;

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

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

| 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;