Secara umum, Subquery adalah sebuah query yang terletak (nested) dalam qeuery lain atau dalam query utama atau outer query.

A. Scalar Subquery.

B. Row Subquery.

C. Subquery and ALL, ANY, EXISTS.

D. Subquery in FROM clause.

Scalar Subqeury adalah subquery yang mengembalikan nilai tunggal. Ini adalah bentuk subquery yang paling sederhana, dan dapat digunakan di sebagian besar tempat jika nilai literal atau kolom tunggal valid.

Tipe data, panjang dan kumpulan karakter serta susunannya, semuanya diambil dari hasil yang dikembalikan oleh subquery. Hasil dari subquery selalu NULL, yaitu tidak ada hasil yang dikembalikan. Meskipun nilai aslinya didefinisikan sebagai NOT NULL, hal ini diabaikan.

Contohnya, Scalar subquery dapat digunakan di dalam SELECT, WHERE clause untuk membandingkan nilai tunggal.

SELECT
    pembeli_id,
    (SELECT SUM(jumlah_pembelian) FROM pembelian WHERE pembeli_id = p.pembeli_id) AS total_pesanan
FROM
    pembelian p
GROUP BY
    pembeli_id;

Maka Hasilnya :

Subquery di dalam SELECT clause, yaitu (SELECT SUM(jumlah_pembelian) FROM pembelian WHERE pembeli_id = p.pembeli_id), menghitung total pesanan untuk setiap pembeli_id. Karena subquery ini mengembalikan satu nilai (total pemeblian).

SELECT
    id_produk,
    judul_produk,
    harga_produk,
    (SELECT AVG(harga_produk) FROM produk) AS average_harga_keseluruhan
FROM
    produk
WHERE
    harga_produk < (SELECT AVG(harga_produk) FROM produk);

Maka hasilnya :

(SELECT AVG(harga_produk) FROM produk) AS average_harga_keseluruhan: Ini adalah scalar subquery yang menghitung rata-rata harga produk untuk seluruh tabel produk dan memberikan hasilnya dengan alias average_harga_keseluruhan.

Dan, harga_produk < (SELECT AVG(harga_produk) FROM produk): Ini adalah perbandingan di dalam klausa WHERE yang membandingkan setiap harga_produk dengan rata-rata harga keseluruhan produk yang dihasilkan oleh scalar subquery.

Row Subquery adalah subquery yang mengembalikan value satu baris/row. Row subquery juga dapat digunakan dalam beberapa clause seperti SELECT, FROM dan IN.

Misalkan ketika ingin menampilkan produk yang memiliki id produk = 1.

SELECT * FROM produk
WHERE
    (judul_produk, deskripsi_produk) = (
        SELECT
            judul_produk,
            deskripsi_produk
        FROM
            produk
        WHERE
            id_produk = 1
    );

Maka hasilnya

apabila dipecah,

SELECT * FROM produk
WHERE
.........

bagian ini merupakan outer query, sedangkan

(judul_produk, deskripsi_produk) = (
        SELECT
            judul_produk,
            deskripsi_produk
        FROM
            produk
        WHERE
            id_produk = 1
    );

Merupakan bagian dari subquery yang mana mencoba mendapatkan judul_produk dan deskripsi_produk dari produk dengan id_produk = 1. Subquery ini mengembalikan satu baris (satu set nilai).

Row Subquery Comparison ((judul_produk, deskripsi_produk) = ...): Bagian ini menggunakan row subquery sebagai satu kesatuan. Jika baris (judul_produk, deskripsi_produk) dalam tabel utama cocok dengan hasil subquery, maka baris tersebut akan termasuk dalam hasil query utama.

Subquery yang menggunakan key ALL akan menghasilkan nilai  true jika perbandingan menghasilkan nilai  true untuk setiap baris yang dikembalikan oleh subquery, atau subquery tidak mengembalikan row/baris.

Syntax :

scalar_expression comparison_operator ALL <Table subquery>

• scalar_expression mungkin ekspresi apa pun yang mengevaluasi ke single value.
• comparison_operator adalah salah satu dari operasi: =, >, <, >=, <=, <> atau !=

SELECT column
FROM  table1
WHERE  column OPERATOR ALL (
  SELECT column
  FROM table2
);

Contoh :

SELECT * FROM pembelian
WHERE jumlah_pembelian < ALL (
    SELECT jumlah_pembelian
    FROM pembelian
    WHERE id_pembelian <> pembelian.id_pembelian
);

Hasil :

mencari pembelian dengan jumlah_pembelian lebih rendah dari semua jumlah_pembelian dalam pembelian lainnya.

ALL mengembalikan:

• NULL jika operator perbandingan mengembalikan NULL untuk setidaknya satu baris yang dihasilkan oleh subquery Tabel atau jika scalar_expression mengembalikan NULL.
• FALSE jika operator perbandingan mengembalikan FALSE untuk setidaknya satu baris yang dihasilkan oleh subquery Tabel.
• TRUE jika operator perbandingan mengembalikan TRUE untuk semua baris yang dihasilkan oleh subquery Tabel, atau jika subquery Tabel tidak mengembalikan baris apa pun.
• NOT IN adalah sinonim untuk <> ALL.

Subquery menggunakan ANY keyword akan mengembalikan true jika perbandingan mengembalikan true untuk setidaknya satu row dikembalikan oleh subquery.

Syntax :

scalar_expression comparison_operator ANY <Table subquery>

• scalar_expression mungkin ekspresi apa pun yang mengevaluasi ke single value.
• comparison_operator adalah salah satu dari operasi: =, >, <, >=, <=, <> or !=

SELECT column
FROM  table1
WHERE column OPERATOR ANY (
  SELECT column
  FROM table2
);

Contoh :

SELECT pembeli_id, produk_id
FROM pembelian
WHERE jumlah_pembelian > ANY (
    SELECT jumlah_pembelian
    FROM pembelian
    WHERE jumlah_pembelian IS NOT NULL
);

mencari pembelian yang memiliki jumlah_pembelian lebih tinggi daripada setidaknya satu pembelian lainnya yang memiliki nilai jumlah_pembelian yang tidak NULL.

ANY mengembalikan:

• TRUE jika operator perbandingan mengembalikan TRUE untuk setidaknya satu baris yang dihasilkan oleh subquery tabel.
• FALSE jika operator perbandingan mengembalikan FALSE untuk semua baris yang dihasilkan oleh subquery tabel, atau jika subquery tabel tidak mengembalikan baris apa pun.
• NULL jika operator perbandingan mengembalikan NULL untuk setidaknya satu baris yang dihasilkan oleh subquery tabel dan tidak mengembalikan TRUE untuk salah satu dari mereka, atau jika scalar_expression mengembalikan NULL.

Contoh Syntax :

SELECT ... WHERE EXISTS <Table subquery>

SELECT column1, column2, ...
FROM table
WHERE EXISTS(
		SELECT column1, column2, ...
		FROM table
		WHERE column
);

Contoh :

SELECT * FROM pembeli
WHERE EXISTS (
  SELECT 1
  FROM pembelian
  WHERE pembelian.pembeli_id = pembeli.id_pembeli
);

memeriksa apakah ada setidaknya satu baris dalam tabel pembelian yang memiliki pembeli_id yang sesuai dengan id_pembeli di tabel pembeli.

Subquery menggunakan kata kunci EXISTS akan mengembalikan nilai benar jika subquery menghasilkan setidaknya satu baris. Sebaliknya, subquery menggunakan NOT EXISTS akan mengembalikan nilai benar hanya jika subquery tidak menghasilkan baris apa pun dari tabel.

Syntax :

select select_list
from (subquery) as table_alias
...;

Sebuah subquery yang muncul dalam klausa FROM umumnya disebut sebagai tabel turunan (derived table). Sebuah tabel turunan harus memiliki alias atau akan mendapatkan kesalahan sintaksis (syntax error).

Contoh :

SELECT
    p1.id_pembeli,
    p1.nama_depan,
    p1.nama_belakang,
    p1.alamat,
    p1.jenis_kelamin,
    p1.tanggal_lahir
FROM
    pembeli p1,
    (SELECT
        jenis_kelamin,
        MAX(tanggal_lahir) AS max_tanggal_lahir
    FROM
        pembeli
    GROUP BY
        jenis_kelamin) AS subquery
WHERE
    p1.jenis_kelamin = subquery.jenis_kelamin
    AND p1.tanggal_lahir = subquery.max_tanggal_lahir;

Hasilnya

Catatan : Menggunakan database modul 7.

1. Andi ingin mengetahui daftar pembelian produk dengan harga paling mahal. (clue row subquery, join tabel)

2. Tampilkan judul produk dan harga produk dari produk yang memiliki harga produk lebih tinggi daripada harga produk dengan id_produk 3 (clue subquery any)

3. Tampilkan rata-rata total penjualan harian pada tanggal 18-10-2023 (clue subquery in from clause)