Correlated Subquery dalam SQL: Cara Kerjanya dengan Contoh

Dalam database relasional, baris sering kali saling bergantung, dan untuk menjawab pertanyaan kompleks, sebuah kueri sering perlu melihat kembali ke tabel yang sedang diprosesnya.

Untuk mengkueri tabel seperti itu, SQL memungkinkan Anda menggunakan correlated subquery, yang mendefinisikan hubungan spesifik di mana kueri bagian dalam bergantung pada nilai dari kueri bagian luar. Sementara subquery standar berjalan sekali lalu selesai, correlated subquery bersifat dinamis, dieksekusi berulang untuk setiap baris yang dievaluasi oleh kueri utama.

Dalam tutorial ini, saya akan menjelaskan bagaimana correlated subquery bekerja dalam SQL, pertimbangan kinerjanya, dan kapan ini merupakan pilihan yang tepat dibandingkan join dan window function. Jika Anda baru di SQL, mulai dari Introduction to SQL kami, atau Intermediate SQL jika Anda sudah memiliki pengalaman. 

Apa Itu Correlated Subquery?

Correlated subquery adalah jenis subquery yang bergantung pada nilai dari kueri luar untuk dijalankan. 

Alih-alih dieksekusi sekali dan mengembalikan hasil tetap, subquery dievaluasi sekali untuk setiap baris yang diproses oleh kueri luar. Ini terjadi karena kueri bagian dalam mereferensikan kolom dari kueri bagian luar, menciptakan tautan langsung antara keduanya.

Sebaliknya, subquery non-terkorelasi berjalan secara independen dari kueri luar. Ia dieksekusi sekali, mengembalikan sekumpulan hasil atau nilai, dan kueri luar menggunakan hasil tersebut tanpa menjalankan ulang subquery untuk setiap baris.

Cara Kerja Correlated Subquery

Alur kerja correlated subquery yang umum di SQL sebagai berikut:

Cara kerja correlated subquery. Gambar oleh Gemini.

• Kueri luar memilih satu baris: SQL mulai memindai tabel di kueri luar dan memilih baris pertama. 
• Referensi: Kueri bagian dalam mengambil nilai dari baris spesifik tersebut, sering kali menggunakan alias.
• Eksekusi: Kueri bagian dalam berjalan menggunakan nilai tersebut.
• Penyaringan/pembaruan: Hasilnya dikirim kembali ke kueri luar untuk menentukan apakah baris tersebut disertakan.
• Iterasi: Proses diulangi untuk baris berikutnya hingga seluruh tabel selesai.

Contoh Correlated Subquery dalam SQL

Sejauh ini, yang saya jelaskan masih konseptual. Cara terbaik untuk belajar adalah melalui contoh.

Contoh 1: Karyawan berpenghasilan di atas rata-rata departemen

Misalkan Anda memiliki tabel employees dengan gaji karyawan dan ID departemen. Anda ingin menemukan karyawan yang berpenghasilan lebih tinggi daripada rata-rata gaji di departemennya.

Gunakan kueri di bawah ini, di mana:

• Kueri luar memilih karyawan dari tabel employees.

• Subquery menghitung rata-rata gaji untuk departemen yang sama.

• Kondisi e2.department_id = e.department_id mereferensikan alias kueri luar e.

 -- Fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)                 -- Calculate the average salary
    FROM employees e2
    WHERE e2.department_id = e.department_id  
    -- Correlation: references the outer query's department_id
);

Contoh 2: Menggunakan EXISTS() dengan correlated subquery

Anda juga dapat menggunakan operator EXISTS() dengan correlated subquery untuk memeriksa apakah ada rekaman terkait di tabel lain.

Misalkan Anda memiliki rekaman di tabel customers dan orders. Anda ingin membuat daftar pelanggan yang telah melakukan setidaknya satu pesanan. Gunakan kueri di bawah ini, di mana:

• Kueri luar memindai baris di tabel customers.

• Subquery memeriksa apakah ada setidaknya satu pesanan untuk pelanggan tersebut.

• Kondisi o.customer_id = c.customer_id menautkan subquery ke kueri luar.

-- Fetch customers with at least one order
SELECT 
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id  
    -- Correlation: references the outer query customer_id
);

Dalam kueri di atas, SQL memeriksa apakah ada baris yang cocok di tabel orders. Jika ada, operator EXISTS() mengembalikan true, dan pelanggan tersebut disertakan dalam hasil.

Correlated vs. Non-Correlated Subquery

Seperti yang telah kita pelajari, subquery dalam SQL terbagi menjadi subquery non-terkorelasi dan correlated subquery. Perbedaan utamanya adalah apakah kueri bagian dalam bergantung pada kueri bagian luar.

Untuk subquery non-terkorelasi, database mengeksekusinya sekali, lalu menggunakan hasilnya di kueri luar.

Sebagai contoh, kueri di bawah ini mencari karyawan yang berpenghasilan lebih tinggi daripada rata-rata gaji keseluruhan.

-- Query employees who earn more than the overall average salary
SELECT 
    employee_id,
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

Dalam kueri di atas, subquery menghitung rata-rata gaji untuk seluruh tabel, dan ia berjalan satu kali. Kueri luar kemudian membandingkan gaji setiap karyawan dengan satu nilai tersebut.

Karena subquery non-terkorelasi berjalan sekali, biasanya lebih cepat ketika hasilnya dapat digunakan kembali. Ini paling baik digunakan untuk perbandingan global, seperti rata-rata dan total keseluruhan.

Namun, correlated subquery bisa lebih lambat pada tabel besar. Mereka berguna ketika kondisi harus dievaluasi relatif terhadap setiap baris, seperti perbandingan tingkat departemen atau pemeriksaan keberadaan data.

Saya merekomendasikan mengikuti  Introduction to SQL Server untuk mempelajari lebih lanjut tentang pengelompokan dan agregasi data, serta menggabungkan tabel.

Correlated Subquery vs. JOIN

Banyak correlated subquery dapat ditulis ulang menggunakan JOIN. Dalam database relasional, JOIN cenderung berkinerja lebih baik karena database dapat memproses hubungan secara set-by-set, bukan baris demi baris.

Pertimbangkan kueri di bawah ini yang menggunakan correlated subquery. Kueri ini membuat daftar karyawan yang dibayar di atas rata-rata gaji dalam departemennya

-- Use subquery to fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Anda dapat menulis ulang kueri menggunakan klausa JOIN untuk menghasilkan hasil yang sama.

-- Use JOIN to fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
JOIN (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    -- Precompute the department average once per department
) dept_avg
ON e.department_id = dept_avg.department_id
-- Match employees with their department averages
WHERE e.salary > dept_avg.avg_salary;
-- Compare salary with the computed department average

Tabel di bawah ini merangkum perbedaan antara correlated subquery dan JOIN dalam SQL.

Saya merekomendasikan mengikuti Joining Data in SQL untuk mempelajari berbagai jenis join di SQL dan cara bekerja dengan beragam tabel terkait dalam database.

Correlated Subquery vs. Window Function

Dalam SQL modern, window function seperti AVG() dan OVER (PARTITION BY) dapat menghitung agregat per baris dalam satu pemindaian. 

Sebagai contoh, kueri di bawah ini mengembalikan karyawan yang gajinya lebih tinggi daripada rata-rata gaji departemennya. Di dalam subquery, digunakan OVER () untuk mengubah agregat menjadi window function dan PARTITION BY department_id untuk membagi tabel menjadi kelompok (partisi) berdasarkan departemen.

-- Use window function to get employees earning more than dept average salary
SELECT 
    employee_id,
    employee_name,
    salary,
    department_id
FROM (
    SELECT 
        employee_id,
        employee_name,
        salary,
        department_id,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
        -- Window function calculates department average once per partition
    FROM employees
) t
WHERE salary > dept_avg_salary;

Namun, correlated subquery tetap berguna saat Anda ingin menggunakan EXISTS() atau NOT EXISTS() untuk menguji hubungan antar tabel. Anda juga dapat menggunakan correlated subquery saat bekerja di database atau situasi di mana window function tidak tersedia.

Kinerja Correlated Subquery

Meskipun correlated subquery kuat, sering kali ada isu kinerja yang menyertainya. 

Correlated subquery dieksekusi berulang

Karena kueri berjalan sekali per baris kueri luar, ini dapat memperlambat kueri pada tabel besar dengan memindai ulang data bagian dalam berkali-kali. Jika tabel luar Anda memiliki 100.000 baris, database melakukan 100.000 sub-tugas.

Bottleneck

Jika Anda tidak melakukan optimasi yang tepat, kueri terkorelasi dapat menyebabkan penggunaan CPU tinggi dan waktu tunggu lama, terutama jika kueri bagian dalam melakukan perhitungan kompleks atau memindai tabel besar.

Pengindeksan kolom

Mengindeks kolom yang digunakan dalam korelasi akan membantu database menemukan baris terkait dalam subquery hampir seketika, alih-alih memindai seluruh tabel bagian dalam setiap kali.

Optimasi Query Planner

Database modern sering mengoptimalkan correlated subquery secara internal. Query planner dapat mengubah kueri menjadi bentuk yang lebih efisien, seperti JOIN atau agregasi yang di-cache, dan secara signifikan mengurangi waktu kueri.

Kapan Menggunakan Correlated Subquery

Anda dapat menggunakan correlated subquery jika ingin melakukan hal-hal berikut:

• Menyaring berdasarkan agregat spesifik baris: Gunakan saat Anda perlu membandingkan nilai relatif terhadap setiap baris, seperti karyawan yang berpenghasilan di atas rata-rata departemennya.

• Memeriksa data terkait dengan EXISTS(): Anda juga dapat menggunakan correlated subquery dengan EXISTS() untuk menguji apakah baris terkait ada.

• Mengekspresikan logika bersarang yang kompleks: Correlated subquery dapat membantu membuat kondisi kompleks lebih mudah dibaca dan diungkapkan dibandingkan rantai JOIN yang panjang.

Namun, hindari menggunakan correlated subquery ketika:

• JOIN sederhana sudah cukup: Jika Anda bisa mendapatkan hasil yang sama dengan LEFT JOIN atau INNER JOIN, gunakan itu, karena umumnya lebih cepat.

• Bekerja dengan big data: Jika kondisi terkorelasi mereferensikan tabel besar tanpa indeks, evaluasi berulang dapat memperlambat kueri secara signifikan.

Kesalahan Umum dengan Correlated Subquery

Berikut adalah beberapa masalah umum yang mungkin Anda temui saat menggunakan correlated subquery, serta cara menanganinya:

• Lupa menambahkan kondisi korelasi: Correlated subquery harus mereferensikan kolom dari kueri luar. Jika kondisi ini hilang, subquery menjadi independen dan dapat menghasilkan hasil yang tidak tepat.
• Salah memahami urutan eksekusi: Selalu ingat bahwa kueri luar berjalan terlebih dahulu dan kueri dalam mengikutinya. Membalik logika ini dalam pikiran Anda dapat menyebabkan hasil yang keliru.
• Nesting yang tidak perlu: Terkadang Anda membungkus nilai sederhana dalam correlated subquery padahal subquery standar sudah cukup. Jika kueri bagian dalam tidak membutuhkan baris luar untuk berfungsi, hilangkan korelasi untuk menghemat kinerja.
• Mengabaikan dampak kinerja: Correlated subquery mungkin berjalan mulus pada dataset kecil, tetapi menjadi lambat seiring pertumbuhan tabel. Untuk menghindarinya, selalu uji kueri Anda dengan ukuran data yang realistis dan pertimbangkan pengindeksan atau menulis ulang kueri jika kinerja menjadi masalah.

Kesimpulan

Mempelajari kapan dan bagaimana menggunakan correlated subquery, serta kapan menggantinya dengan teknik lain, adalah keterampilan penting untuk menulis kueri SQL yang jelas dan efisien.

Sebagai langkah berikutnya, saya merekomendasikan memperoleh SQL Associate Certification untuk menunjukkan penguasaan Anda dalam menggunakan SQL untuk analisis data dan menonjol di antara profesional data lainnya. Terakhir, saya merekomendasikan mengikuti kursus Database Design, di mana Anda akan belajar membuat dan mengelola database serta memilih DBMS yang sesuai dengan kebutuhan Anda.