Macam-Macam Operator Logika
Berikut ini adalah tabel macam-macam operator logika yang terdapat pada MySQL:
| Operator | Deskripsi |
|---|---|
| AND | Menggunakan logika AND didalam WHERE clause |
| OR | Menggunakan logika OR didalam WHERE clause |
| IN | Akan membandingkan data dengan list yang telah ditentukan |
| BETWEEN | Akan membandingkan data pada range yang telah ditentukan |
| NOT | Menggunakan logika NOT, seperti NOT IN, NOT BETWEEN, NOT LIKE, dan lain sebagainya |
| LIKE | Membandingkan data yang sesuai dengan pola tertentu (wildcard) |
| IS NULL | Membandingkan apakah data sama dengan null? |
Contoh
Misalkan Anda memiliki sebuah table "tbl_employee" dengan data-data sebagai berikut:
mysql> select * from tbl_employee;
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
| 1 | Ahmad Sofyan | 1990-01-01 | Bandung | 2250000.00 | IT | L |
| 2 | Ismail Saleh | 1986-12-12 | Pandeglang | 5000000.00 | ACC | L |
| 3 | Ferry Subekti | 1987-06-06 | Purwakarta | 4500000.00 | HRD | P |
| 4 | Iskiyati | 1988-07-07 | Brebes | 7500000.00 | FIN | P |
| 5 | Nurul Hikmah | 1989-08-08 | Purwokerto | 3750000.00 | FIN | P |
| 6 | Riyanto | 1983-02-02 | Subang | 4500000.00 | IT | L |
| 7 | Azka Nurul | 1984-03-03 | Bandung | 5000000.00 | HRD | P |
| 8 | Venny Maya | 1989-04-04 | Jakarta | 7000000.00 | IT | P |
| 9 | Ana Fauziyah | 1989-05-05 | Bekasi | 3700000.00 | ACC | P |
| 10 | Arya Adhi | 1980-09-09 | Magelang | 8000000.00 | FIN | L |
+-------------+---------------+------------+-------------+------------+------------+--------+
10 rows in set (0.00 sec)
Dari data diatas,
- Tampilkan semua data yang memiliki departement 'IT' dan jenis kelamin laki-laki
mysql> SELECT * FROM tbl_employee
-> WHERE department = 'IT'
-> AND gender = 'L';
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
| 1 | Ahmad Sofyan | 1990-01-01 | Bandung | 2250000.00 | IT | L |
| 6 | Riyanto | 1983-02-02 | Subang | 4500000.00 | IT | L |
+-------------+---------------+------------+-------------+------------+------------+--------+
2 rows in set (0.00 sec) - Tampilkan semua data yang memiliki departement 'IT' atau jenis kelamin laki-laki
mysql> SELECT * FROM tbl_employee
-> WHERE department = 'IT'
-> OR gender = 'L';
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
| 1 | Ahmad Sofyan | 1990-01-01 | Bandung | 2250000.00 | IT | L |
| 2 | Ismail Saleh | 1986-12-12 | Pandeglang | 5000000.00 | ACC | L |
| 6 | Riyanto | 1983-02-02 | Subang | 4500000.00 | IT | L |
| 8 | Venny Maya | 1989-04-04 | Jakarta | 7000000.00 | IT | P |
| 10 | Arya Adhi | 1980-09-09 | Magelang | 8000000.00 | FIN | L |
+-------------+---------------+------------+-------------+------------+------------+--------+
5 rows in set (0.00 sec) - Tampilkan semua data yang memiliki nama dengan huruf depan 'A'
mysql> SELECT * FROM tbl_employee
-> WHERE employee_name like 'A%';
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
| 1 | Ahmad Sofyan | 1990-01-01 | Bandung | 2250000.00 | IT | L |
| 7 | Azka Nurul | 1984-03-03 | Bandung | 5000000.00 | HRD | P |
| 9 | Ana Fauziyah | 1989-05-05 | Bekasi | 3700000.00 | ACC | P |
| 10 | Arya Adhi | 1980-09-09 | Magelang | 8000000.00 | FIN | L |
+-------------+---------------+------------+-------------+------------+------------+--------+
4 rows in set (0.00 sec) - Tampilkan semua data yang memiliki departement di 'IT', 'FIN', dan 'HRD'
mysql> SELECT * FROM tbl_employee
-> WHERE department in ('IT','FIN','HRD');
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
| 1 | Ahmad Sofyan | 1990-01-01 | Bandung | 2250000.00 | IT | L |
| 3 | Ferry Subekti | 1987-06-06 | Purwakarta | 4500000.00 | HRD | P |
| 4 | Iskiyati | 1988-07-07 | Brebes | 7500000.00 | FIN | P |
| 5 | Nurul Hikmah | 1989-08-08 | Purwokerto | 3750000.00 | FIN | P |
| 6 | Riyanto | 1983-02-02 | Subang | 4500000.00 | IT | L |
| 7 | Azka Nurul | 1984-03-03 | Bandung | 5000000.00 | HRD | P |
| 8 | Venny Maya | 1989-04-04 | Jakarta | 7000000.00 | IT | P |
| 10 | Arya Adhi | 1980-09-09 | Magelang | 8000000.00 | FIN | L |
+-------------+---------------+------------+-------------+------------+------------+--------+
8 rows in set (0.00 sec) - Tampilkan semua data yang memiliki gaji berkisar antara Rp 4.000.000 sampai dengan Rp 7.000.000
mysql> SELECT * FROM tbl_employee
-> WHERE salary BETWEEN 4000000 AND 7000000;
+-------------+---------------+------------+-------------+------------+------------+--------+
| employee_id | employee_name | birth_date | birth_place | salary | department | gender |
+-------------+---------------+------------+-------------+------------+------------+--------+
| 2 | Ismail Saleh | 1986-12-12 | Pandeglang | 5000000.00 | ACC | L |
| 3 | Ferry Subekti | 1987-06-06 | Purwakarta | 4500000.00 | HRD | P |
| 6 | Riyanto | 1983-02-02 | Subang | 4500000.00 | IT | L |
| 7 | Azka Nurul | 1984-03-03 | Bandung | 5000000.00 | HRD | P |
| 8 | Venny Maya | 1989-04-04 | Jakarta | 7000000.00 | IT | P |
+-------------+---------------+------------+-------------+------------+------------+--------+
5 rows in set (0.03 sec)
Sekian tutorial singkat tentang Pengenalan Operator Logika Pada MySQL. Semoga bermanfaat & Happy Learning MySQL Database.
Salam,
Nursalim
Bagikan
Pengenalan Operator Logika Pada MySQL
4/
5
Oleh
Kris Kimcil
