MR Ferry

Gw pernah baca salah satu postingan di Threads yang ngasih tips nulis query “Jangan gunakan subquery!” beberapa bulan lalu tanpa menjabarkan konteksnya. Ini juga jadi salah satu hal yang diamini banyak orang sebagai hal buruk. Tanpa konteks yang jelas pernyataan itu dapat membingungkan orang-orang dan bikin salah kaprah. Apakah benar subquery itu jelek? Jawabannya bisa iya dan bisa tidak. Ada beberapa kasus di mana justru subquery lebih efisien dibanding alternatif lainnya. Tapi ga dipungkiri juga, memang ada kasus di mana subquery membuat performa jauh lebih buruk.

Subquery

Secara definisi, Subquery adalah query yang terdapat di dalam sebuah statement sql lainnya. Jadi di sebuah statement sql terdapat lagi sebuah SELECT statement lain di dalamnya. Dengan begitu, kita bisa melakukan lebih dari satu operasi dalam sekali eksekusi. Ada beberapa cara untuk melakukan subquery. Untuk mempraktekkannya kita akan menggunakan contoh kasus pada database klub dan pemain sepak bola.

Tabel clubs

Tabel players

Derived Table (Inline Views)

Derived Table ataupun Inline Views adalah istilah yang merujuk pada hal yang sama, yaitu sebuah tabel replika yang dihasilkan dari sebuah subquery untuk menampung data sementara yang akan digunakan kembali pada statement lainnya. Misalnya kita ingin menampilkan data nama-nama klub, jumlah gaji pemain, status eligibilitas klub yang ditentukan lewat jumlah gaji pemainnya yang tidak melebihi rules max salary klub tersebut seperti aturan gaji pemain di Liga Spanyol, dan minimal jumlah gajinya Rp200.000.000 ke atas.

SELECT
    tmp.name,
    tmp.sum_salary,
    (tmp.sum_salary <= max_total_salary) eligible
FROM (SELECT
          c.name             name,
          sum(p.salary)      sum_salary,
          c.max_total_salary max_total_salary
      FROM clubs c
               JOIN players p
      ON c.id = p.club_id
      GROUP BY c.id) AS tmp
WHERE
    tmp.sum_salary >= 200000000
;

Dengan Derived Table kita hanya perlu melakukan operasi penjumlahan gaji sekali di dalam subquery, lalu data tersebut kita simpan pada data replika dan menampilkan data pada query berikutnya berdasarkan data replika tersebut tanpa perlu melakukan operasi penjumlahan lagi👍. Tanpa Derived Table ini ga efisien.

SELECT
    c.name,
    sum(p.salary),
    (sum(p.salary) <= c.max_total_salary) eligible
FROM clubs c
         JOIN players p
ON c.id = p.club_id
GROUP BY c.id
HAVING
    sum(p.salary) >= 200000000
;

Pada query di atas penjumlahan gaji dilakukan setidaknya 3 kali, yaitu saat mendapatkan data jumlah gaji, menentukan status eligible, dan filter jumlah gaji👎. Dalam hal ini menggunakan subquery jauh lebih baik walaupun query-nya jadi sedikit verbose.

Subquery in Select

Sesuai namanya, subquery ini ditulis di bagian SELECT statement. Misalkan kita ingin menampilkan data nama-nama klub beserta jumlah pemainnya.

SELECT
    c.name,
    (SELECT count(p.id) FROM players p WHERE p.club_id = c.id) players_count
FROM clubs c
;

Di sql, statement SELECT adalah statement paling akhir yang dieksekusi oleh database. Pada query di atas, yang pertama kali dilakukan adalah database akan mencari data klub, lalu pada masing-masing data klub akan dilakukan lagi query untuk menghitung jumlah pemain pada klub tersebut satu-persatu. Secara kompleksitas ini kurang efisien👎. Untuk hal ini, alternatifnya menggunakan Join biasa dengan GROUP BY statement.

SELECT
    c.name,
    count(p.id) players_count
FROM clubs c
         JOIN players p
ON c.id = p.club_id
GROUP BY c.id
;

Dengan begini query-nya lebih baik secara kompleksitas karena ga perlu subquery untuk menghitung jumlah pemain satu-persatu👍.

Uncorrelated Subquery

Uncorrelated artinya ketika subquery tersebut tidak berkorelasi dengan statement lainnya. Dengan kata lain, subquery tersebut independen dan statement-nya tidak berkaitan dengan kolom pada statement lain. Misalkan dari data di atas kita ingin menampilkan pemain yang gajinya lebih tinggi dari pemain termahal “Barcelona”.

SELECT
    p.name,
    p.salary
FROM players p
WHERE
    p.salary > (SELECT
                    max(p2.salary)
                FROM clubs c
                         JOIN players p2
                ON c.id = p2.club_id
                WHERE
                    c.name = 'Barcelona')
;

Subquery di atas tidak berkorelasi dengan kolom query utama, melainkan sebuah subquery yang independen. Subquery tersebut akan diekesekusi terlebih dahulu sekali oleh database. Hasil dari subquery itulah yang nanti akan digunakan pada query utama untuk filter gaji. Dalam hal ini subquery bukanlah masalah karena hanya akan dieksekusi sekali👍.

Correlated Subquery

Ini adalah kebalikan dari Uncorrelated Subquery. Correlated Subquery artinya terdapat dependensi pada subquery tersebut dengan kolom pada statement lain. Misalnya kita ingin menampilkan nama-nama pemain yang gajinya di atas rata-rata gaji pemain di masing-masing klub mereka dan pernah mencetak gol.

SELECT
    p.name,
    p.salary
FROM players p
WHERE
    p.salary > (SELECT avg(p2.salary) FROM players p2 WHERE p2.club_id = p.club_id AND p.scored > 0)
;

Pada subquery di atas, terdapat dependensi pada kolom club_id dengan kolom club_id pada query utama. Inilah yang disebut “berkorelasi” dan dapat membuat performa query turun karena tiap masing-masing data pemain yang dibaca akan dilakukan query lagi satu-persatu untuk mencari rata-rata gaji pemain di klub pemain tersebut👎. Alternatifnya menggunakan subquery dengan Derived Table.

SELECT
    p.name,
    p.salary
FROM players p
         JOIN (SELECT p2.id, avg(p2.salary) avg_salary FROM players p2 WHERE p.scored > 0 GROUP BY p2.club_id) tmp
ON p.id = tmp.id
WHERE
    p.salary > tmp.avg_salary
;

Dengan Derived Table kita bisa menyimpan data rata-rata gaji pemain tiap klub dalam sekali query, lalu tabel yang berisi data rata-rata gaji itulah yang kita join dengan data dari tabel players. Dengan begini database ga perlu melakukan query berulang kali tiap masing-masing pemain👍.

Semi Join

Semi Join adalah melakukan query untuk menampilkan data yang hanya berelasi dengan tabel lain. Misalnya kita ingin menampilkan nama-nama klub yang memiliki minimal satu pemain. Kita bisa melakukan Semi Join menggunakan function exists().

SELECT
    c.name
FROM clubs c
WHERE
    exists(SELECT 1 FROM players p WHERE p.club_id = c.id)
;

Meskipun terdapat korelasi di dalam subquery dengan kolom dari statement lain saat melakukan filter club_id, tapi hal ini bukanlah masalah karena khusus Semi Join akan di-optimize oleh database. Dengan Semi Join, data yang akan dibandingkan hanya salah satu data, bukan semua data. Pada tabel di atas, antara tabel clubs dan players terdapat one-to-many relationship. Saat melakukan filter, database hanya akan scan salah satu pemain saja masing-masing klub untuk mengecek apakah klub ini punya pemain atau tidak. Makanya ini lebih baik karena data yang di-join hanya salah satu data saja sehingga lebih optimal👍. Beda halnya kalau kita menggunakan Inner Join.

SELECT DISTINCT
    c.name
FROM clubs c
         JOIN players p
ON c.id = p.club_id
;

Pada query di atas database akan menggabungkan semua data yang berelasi terlebih dahulu. Kemudian data tersebut akan di-filter untuk membuang data duplikat lewat keyword DISTINCT karena relasinya one-to-many relationship👎. Sehingga dalam hal ini menggunakan subquery dengan Semi Join lebih efisien.

Anti Join with NOT IN

Anti Join adalah melakukan query untuk menampilkan data yang tidak berelasi dengan tabel lain. Misalnya kita ingin menampilkan nama-nama pemain yang bukan pemain “Arsenal”.

SELECT
    p.name
FROM players p
WHERE
    p.club_id NOT IN (SELECT c.id FROM clubs c WHERE c.name = 'Arsenal')
;

Hasil dari query tersebut adalah seperti berikut:

Tidak ada nama “Pogba” di sana, padahal dia juga bukan pemain “Arsenal”. Ini karena operator NOT IN tidak bisa menghandle null👎. Pogba yang tidak punya klub ga muncul sehingga data yang ditampilkan salah. Untuk itu lebih baik gunakan Anti Join dengan function NOT exists().

SELECT
    p.name
FROM players p
WHERE
    NOT exists(SELECT 1 FROM clubs c WHERE c.id = p.club_id AND c.name = 'Arsenal')
;

Meskipun berkorelasi, tapi subquery dengan Anti Join menggunakan function NOT exists() juga di-optimize oleh database seperti Semi Join. Sekarang nama “Pogba” sudah muncul pada data pemain yang bukan pemain “Arsenal”👍.

Verdict

Subquery tidak sepenuhnya buruk. Memang ada kasus yang membuat performa query jadi sangat buruk saat menggunakan subquery, tapi ada juga kasus yang justru menggunakan subquery jadi lebih efisien. Jadi kita ga bisa menghakimi subquery itu sebagai hal haram karena ada banyak faktor yang membuat sebuah query itu lambat. Bisa jadi query yang dianggap jelek itu secara cost lebih efisien. Selain itu, jangan lupa lakukan analisa dalam query lewat keyword EXPLAIN untuk mengecek cost query. Bisa jadi penyebab lambat sebuah query itu karena hal lain, seperti tabel yang ga ter-index dengan baik, query-nya melakukan full scan, range data yang terlalu banyak, filter yang ga tepat sasaran, atau berbagai hal lainnya. Pastikan juga hasil operasi dari subquery tersebut datanya ga terlalu besar.