mysql> use mahasiswa;
Database changed

mysql> select nim,nama from mhs where exists
    -> (select * from khs where nim = mhs.nim);
+------------+--------+
| nim        | nama   |
+------------+--------+
| 0802100011 | emy    |
| 0802100012 | waldan |
| 0802100013 | tasya  |
| 0802100014 | rika   |
| 0802100015 | thomas |
+------------+--------+
5 rows in set (0.14 sec)

mysql> select nim,nama from mhs
    -> where nim = any(select distinct nim from khs);
+------------+--------+
| nim        | nama   |
+------------+--------+
| 0802100011 | emy    |
| 0802100012 | waldan |
| 0802100013 | tasya  |
| 0802100014 | rika   |
| 0802100015 | thomas |
+------------+--------+
5 rows in set (0.00 sec)

mysql> select nim,nama from mhs
    -> where nim in(select distinct nim from khs);
+------------+--------+
| nim        | nama   |
+------------+--------+
| 0802100011 | emy    |
| 0802100012 | waldan |
| 0802100013 | tasya  |
| 0802100014 | rika   |
| 0802100015 | thomas |
+------------+--------+
5 rows in set (0.01 sec)

mysql> select nim,nama from mhs
    -> where not exists(select * from khs where nim = mhs.nim);
Empty set (0.00 sec)

DATABASE PERUSAHAAN

mysql> select no_karyawan,Nama from Karyawan where exists
    -> (select * from Teknisi_proyek where no_karyawan = Teknisi_proyek.no_karya
wan);
+-------------+---------+
| no_karyawan | Nama    |
+-------------+---------+
| A           | mega    |
| B           | andi    |
| C           | sinta   |
| D           | Alisa   |
| E           | Angkasa |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> select no_karyawan,Nama from Karyawan where no_karyawan = any
    -> (select distinct no_karyawan from Teknisi_proyek);
+-------------+---------+
| no_karyawan | Nama    |
+-------------+---------+
| A           | mega    |
| B           | andi    |
| C           | sinta   |
| D           | Alisa   |
| E           | Angkasa |
+-------------+---------+
5 rows in set (0.00 sec)
mysql> select no_karyawan,Nama from Karyawan where no_karyawan in
    -> (select distinct no_karyawan from Teknisi_proyek);

+-------------+---------+
| no_karyawan | Nama    |
+-------------+---------+
| A           | mega    |
| B           | andi    |
| C           | sinta   |
| D           | Alisa   |
| E           | Angkasa |
+-------------+---------+
5 rows in set (0.00 sec)

mysql> select no_karyawan,Nama from Karyawan where not exists
    -> (select * from Teknisi_proyek where no_karyawan=Karyawan.no_Karyawan);
Empty set (0.00 sec)

Kesimpulan :
Subquery IN : subquery yang digunakan untuk menghasilkan 1(satu) kolom tapi boleh beberapa baris.
Subquery EXISTS : subquery yang dapat menghasilkan satu atau lebih kolom.
Subquery ALL/SOME : Subquery yang mana hasil yang dikembalikan lebih dari satu.

No related posts.