SubQuery

SubQuery
Subquery adalah perintah SELECT di dalam perintah lain. Mulai dari MySQL 4.1, semua bentuk subquery dan operasinya dalam SQL standar sudah didukung.
Berikut adalah contoh subquery:

SELECT * FROM tabalA WHERE kolom1=(SELECT kolom1 FROM tabelB);

Contoh di atas, SELECT * FROM tabalA merupakan query di luar (perintah outer), sedangkan SELECT kolom1 FROM tabelB merupakan subquery. Dapat dikatakan bahwa subquery adalah bersarang di dalam query luar (outer query).
Kegunaan dari subquery adalah:
1. Mengijinkan query-query yang terstruktur sehingga memungkinkan permisahan setiap bagian dari perintah
2. Menyediakan cara alternatif untuk membentuk operasi yang di sisi lain memerlukan join dan union yang rumit
3. Mudah dibaca

Sebuah subquery bisa mengembalikan nilai skalar (nilai tunggal), baris tunggal, kolom tunggal, atau tabel. Subquery bisa mengandung query yang berisi klausa : DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, UNION dan lainnya.
Subquery Sebagai Operand Skalar
Subquery skalar adalah subquery yang paling sederhana yang mengembalikan nilai tunggal.
Berikut adalah contoh subquery sebagai operand skalar:

create table contoh1(
id int,
nama char(10)
);

insert into contoh1 values(1,'sukrisno');

setelah tabel dibuat, jalankan perintah SELECT berikut ini:

SELECT (select nama from contoh1)

Pertama kali, query yang dijalankan adalah (select nama from contoh1) dengan nilai yang dikembalikan adalah ‘sukrisno’ yang bertipe char(10), kemudian hasil select ini akan di select lagi oleh perintah select luar.

Subquery ini juga bisa mengandung fungsi-fungsi, dan yang harus diperhatikan adalah penambahan fungsi tersebut harus menambahkan tanda kurung seperti contoh berikut ini:

SELECT upper((select nama from contoh1))
Operator-operator pembanding yang biasa digunakan dalam subquery antara lain: = > < >= <= <>

2. Subquery dengan ANY, IN, SOME dan ALL
Subquery sering dipadukan dengan perintah any, in, some dan all.
Kata kunci any, yang harus mengikuti operator pembanding, yang berarti “mengembalikan nilai TRUE jika pembanding adalah TRUE untuk SETIAP nilai dalam kolom yang dikembalikan oleh subquery”. Sebagai contoh:

Select kolom1 from tabel1 where kolom1 > ANY (select kolom1 from tabel2)

Misalkan kolom1 dalam tabel1 mempunyai data (10). Perintah ini akan mengembalikan nilai true jika kolom1 dalam tabel2 mengandung nilai seperti (22, 13, 9) karena terdapat nilai 9 yang lebih kecel dari 10. Perintah ini akan mengembalikan nilai false jika kolom1 dalam tabel2 berisi data (22,10), atau jika kolom1 dalam tabel2 kosong. Perintah ini akan dinyatakan UNKNOWN jika kolom1 dalam tabel2 berisi nilai (NULL, NULL, NULL).

Kata kunci IN merupakan alias dari ANY, sehingga kedua kata kunci ini adalah sama, seperti contoh:

Select kolom1 from tabel1 where kolom1 = ANY (select kolom1 from tabel2)
Select kolom1 from tabel1 where kolom1 IN (select kolom1 from tabel2)

Namun, NOT IN bukan pengganti dari <> ANY, tetapi untuk <> ALL. Untuk keperluan <> ANY, digunakan perintah SOME (<> SOME) yang merupakan alias dari ANY seperti contoh berikut:

Select kolom1 from tabel1 where kolom1 <> ANY (select kolom1 from tabel2)
Select kolom1 from tabel1 where kolom1 <> SOME (select kolom1 from tabel2)

kata kunci ALL, yang harus mengikuti operator pembanding, yang berarti “mengembalikan nilai TRUE jika pembanding TRUE untuk SEMUA nilai dalam kolom yang dikembalikan oleh subquery. Sebagai contoh:

Select kolom1 from tabel1 where kolom1 > ALL (select kolom1 from tabel2)

Misalkan di kolom1 dalam tabel1 berisi nilai (10). Perintah ini akan mengembalikan nilai TRUE jika kolom1 dalam tabel2 mengandung nilai (9, 0, -9) karena nilai 10 lebih besar dari ketiga nilai dalam tabel2. Perintah akan mengembalikan nilai FALSE jika kolom1 dalam tabel2 mengandung nilai (22, 9, 5) karena terdapat nilai 22 yang lebih besar dari 10.
Namun, jika tabel2 kosong, maka nilai yang dikembalikan adalah TRUE.
NOT IN adalah pengganti dari <> ALL, sehingga kedua perintah berikut ini adalah sama:
Select kolom1 from tabel1 where kolom1 <> ALL (select kolom1 from tabel2)
Select kolom1 from tabel1 where kolom1 NOT IN (select kolom1 from tabel2)
3. Subquery Baris
Subquery baris adalah subquery yang mengembalikan baris tunggal dan bisa mengembalikan lebih dari satu nilai kolom, seperti contoh berikut ini:

Select * from tabel1 where (kolom1, kolom2) = (5,9)
Query tersebut akan bernilai true jika kolom1 dari tabel1 adalah 5 dan kolom2 dari tabel1 adalah 9.
Query tersebut juga dapat dinyatakan dengan perintah
Select * from tabel1 where kolom1=5 and kolom2=9
4. subquery dalam klausa FROM
Subquery bisa digunakan dalam klausa FROM. Sebagai contoh berikut ini:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);INSERT INTO t1 VALUES (1,'1',1.0);INSERT INTO t1 VALUES (2,'2',2.0);SELECT sb1,sb2,sb3FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sbWHERE sb1 > 1;
Hasil: 2, '2', 4.0.
Contoh lain dari pemakaian subquery dalam FROM, jika kita ingin mencari data yang paling tinggi (MAX) dari total setiap nilai (SUM), seperti berikut ini:

create table penjualan(
no_nota char(5),
kode_brg char(5),
jumlah_jual int,
harga_jual numeric(10,2)
);

insert into penjualan values ('N-1','A-1',12,120000);
insert into penjualan values ('N-2','A-1',3,125000);
insert into penjualan values ('N-3','B-1',3,15000);
insert into penjualan values ('N-4','B-1',7,15000);
insert into penjualan values ('N-5','C-1',2,85000);

misalnya, untuk mencari kode barang dan total jumlah jual yang total jumlah jualya paling banyak.
Jika menggunakan perintah:
select max(sum(jumlah_jual)) from penjualan group by kode_brg
perintah tersebut tidak bisa dijalankan, sehingga digunakan subquery seperti berikut ini:
SELECT MAX(total_jumlah_jual)
FROM
(SELECT SUM(jumlah_jual) AS total_jumlah_jual FROM penjualan GROUP BY kode_brg)
as Total