Subquery SQL: Panduan Komprehensif

Subquery SQL adalah alat yang kuat dalam manajemen basis data, memungkinkan pengambilan data yang lebih kompleks dan efisien. Panduan ini akan membawa Anda memahami dasar-dasar subquery SQL, memberikan wawasan tentang penerapan praktis dan teknik tingkat lanjutnya. Baik Anda pemula maupun profesional berpengalaman, menguasai subquery dapat secara signifikan meningkatkan keterampilan SQL Anda.

Bagi Anda yang baru memulai SQL, pertimbangkan untuk belajar dari kursus Intermediate SQL untuk membangun fondasi yang kuat. Selain itu, saya merasa SQL Basics Cheat Sheet yang dapat Anda unduh merupakan referensi yang membantu karena memuat semua fungsi SQL paling umum. Terakhir, saya ingin menyampaikan bahwa subquery adalah pertanyaan wawancara SQL yang sering muncul, jadi jika Anda sedang mempersiapkan wawancara, Anda berada di tempat yang tepat untuk melakukan ulasan.

Apa itu Subquery SQL?

Subquery membuat kueri SQL lebih modular dengan menangani tugas yang jika tidak akan memerlukan beberapa kueri terpisah.

Definisi dan tujuan

Subquery SQL adalah kueri yang disarangkan di dalam kueri SQL lain, digunakan untuk melakukan operasi yang memerlukan beberapa langkah atau logika kompleks. Peran subquery dalam SQL mencakup hal-hal berikut:

• Memfilter rekam data berdasarkan data dari tabel terkait.
• Mengagregasi data dan melakukan perhitungan secara dinamis.
• Saling merujuk data antar tabel untuk mendapatkan wawasan spesifik.
• Memilih baris secara kondisional tanpa memerlukan join eksplisit atau logika kode eksternal.

Kedengarannya banyak, tetapi semuanya akan masuk akal saat kita membahasnya dalam tutorial ini.

Jenis-jenis subquery

Mungkin mengejutkan Anda bahwa ada berbagai jenis subquery. Jenis-jenis ini dikelompokkan dan disesuaikan untuk berbagai kebutuhan pengambilan data. Anda dapat memilih dari subquery berikut bergantung pada operasi yang ingin dilakukan:

Subquery skalar

Subquery skalar mengembalikan satu nilai, seperti satu baris dan satu kolom. Subquery ini sering digunakan ketika diharapkan satu nilai, misalnya dalam perhitungan, perbandingan, atau penetapan pada klausa SELECT atau WHERE.

Pada contoh di bawah, subquery skalar (SELECT AVG(salary) FROM employees) mengembalikan satu nilai, yaitu gaji rata-rata, dan membandingkannya dengan gaji masing-masing karyawan.

-- Example of Scalar Subquery 
-- Compares each salary to the average salary
SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS average_salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery kolom

Subquery kolom mengembalikan satu kolom namun beberapa baris. Subquery ini sering digunakan dengan operator seperti IN atau ANY, di mana kueri luar membandingkan nilai dari beberapa baris.

Misalnya, subquery di bawah mengembalikan daftar ID departemen untuk departemen yang berlokasi di New York, yang kemudian digunakan kueri utama untuk memfilter karyawan di departemen tersebut.

-- Example of Column Subquery 
-- Filters based on departments in New York
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

Subquery baris

Subquery baris mengembalikan satu baris yang berisi beberapa kolom. Subquery ini biasanya digunakan dengan operator perbandingan yang dapat membandingkan satu baris data, seperti operator = atau IN, ketika diharapkan beberapa nilai.

Subquery berikut mengambil departemen dan jabatan seorang manajer, dan kueri luar mencari karyawan dengan nilai yang cocok.

-- Example of Row Subquery 
-- Matches department and job title with a specific manager
SELECT employee_name
FROM employees
WHERE (department_id, job_title) = (SELECT department_id, job_title FROM managers WHERE manager_id = 1);

Subquery tabel (tabel turunan)

Subquery tabel, atau tabel turunan, mengembalikan sebuah tabel lengkap yang terdiri dari banyak baris dan kolom. Ini umum digunakan di klausa FROM sebagai tabel sementara dalam sebuah kueri.

Sebagai contoh, subquery di bawah membuat tabel turunan berupa rata-rata gaji per departemen, yang kemudian digunakan di kueri luar untuk menemukan departemen dengan gaji rata-rata di atas ambang tertentu.

-- Example of Table Subquery 
-- Uses derived table for average department salary comparison
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM 
    (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
WHERE dept_avg.avg_salary > 50000;

Memahami Subquery SQL

Dari contoh di atas, kita melihat bahwa subquery diapit oleh tanda kurung. Sekarang mari kita telusuri mekanisme subquery, termasuk sintaks dan urutan eksekusinya.

Sintaks dan struktur

Sintaks subquery bervariasi bergantung pada tempat penggunaannya dalam pernyataan SQL utama, seperti di dalam klausa SELECT, FROM, atau WHERE. Subquery biasanya diapit oleh tanda kurung ( ), menandakan sebuah kueri terpisah. 

Contoh berikut menunjukkan subquery di dalam klausa WHERE, yang memungkinkan kita memfilter data pada kueri utama berdasarkan hasil dari kueri bersarang. 

-- Selects the main column to retrieve from the main table to query
SELECT column_name
FROM table_name
-- Applies a condition to filter rows based on the subquery result
WHERE column_name operator 
      -- Subquery retrieves data for comparison in the WHERE clause
      (SELECT column_name FROM table_name WHERE condition);  

Urutan eksekusi

Urutan eksekusi untuk subquery bergantung pada apakah subquery tersebut berkorelasi atau tidak berkorelasi.

Subquery tidak berkorelasi

Subquery tidak berkorelasi bersifat independen dari kueri luar dan dieksekusi terlebih dahulu. Hasil subquery kemudian diteruskan ke kueri luar. Subquery tidak berkorelasi umum digunakan untuk perhitungan dan filter pada level skalar atau kolom.

Kueri di bawah mengikuti urutan eksekusi:

• Subquery (SELECT AVG(salary) FROM employees) berjalan terlebih dahulu dan menghitung gaji rata-rata.

• Kueri luar kemudian mengambil karyawan yang gajinya lebih besar dari rata-rata tersebut.

-- Retrieves names of employees with above-average salary
SELECT employee_name  
FROM employees
-- Subquery: calculates average salary across all employees
WHERE salary > (SELECT AVG(salary) FROM employees);  

Saya merekomendasikan kursus Introduction to SQL Server dari DataCamp untuk mempelajari lebih lanjut tentang pengelompokan dan agregasi data, serta join antar tabel.

Subquery berkorelasi

Subquery berkorelasi bergantung pada kueri luar untuk sebagian datanya, sehingga dievaluasi ulang untuk setiap baris yang diproses kueri luar. 

Kueri berikut dieksekusi dengan urutan ini:

• Untuk setiap baris di employees (dialias sebagai e1), subquery (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) menghitung gaji rata-rata untuk departemen tersebut.

• Kueri luar kemudian membandingkan gaji setiap karyawan dengan gaji rata-rata departemennya dan hanya menyertakan mereka yang berpenghasilan lebih tinggi.

-- Retrieves names of employees with above-average salary in their department
SELECT e1.employee_name  
FROM employees e1
 -- Subquery: calculates average salary for each department
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id); 

Penerapan Subquery SQL

Subquery SQL penting untuk pengambilan dan transformasi data, memungkinkan Anda menulis kueri kompleks dan menangani tugas analisis data tingkat lanjut. Berikut adalah penerapan subquery di dunia nyata dalam manajemen basis data.

Memfilter data

Subquery berguna ketika memfilter data berdasarkan kondisi dinamis, terutama saat pemfilteran memerlukan perbandingan nilai di beberapa tabel atau melakukan perhitungan.

Subquery berikut mengambil category_id dari "Product A", dan kueri utama menemukan semua produk dalam kategori tersebut.

-- Retrieves names of products in the same category as 'Product A'
SELECT product_name  
FROM products
 -- Subquery: finds category ID of 'Product A'
WHERE category_id = (SELECT category_id FROM products WHERE product_name = 'Product A'); 

Agregasi data

Subquery juga digunakan untuk agregasi data, terutama saat menghasilkan statistik ringkas atau wawasan untuk pelaporan dan analisis. Subquery (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) menghitung rata-rata penjualan per departemen. Kueri luar kemudian memfilter departemen dengan penjualan rata-rata di atas 50.000. 

-- Retrieves department IDs and their average sales
-- Filters for departments with average sales over 50,000
SELECT department_id, avg_sales  
FROM (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) AS dept_sales  -- Subquery: calculates average sales per department
WHERE avg_sales > 50000;  

Pertimbangan Kinerja dan Praktik Terbaik

Meskipun subquery kuat untuk menulis kueri kompleks, subquery dapat berdampak pada kinerja, terutama saat bekerja dengan dataset besar. Penting untuk mempertimbangkan jebakan umum dan praktik terbaik untuk meningkatkan kinerja.

Mengoptimalkan kinerja subquery

Mengoptimalkan kinerja subquery memastikan waktu eksekusi kueri dan responsivitas basis data yang lebih baik. Berikut adalah cara-cara untuk mengoptimalkan subquery.

• Indeks Kolom yang Relevan: Untuk mempercepat pengambilan data, pastikan kolom yang digunakan dalam klausa WHERE dan JOIN serta operasi perbandingan diindeks.

• Batasi Penggunaan Subquery Berkorelasi: Jika memungkinkan, gunakan operasi JOIN atau CTE alih-alih subquery berkorelasi, karena sering kali dapat memproses data lebih cepat dengan operasi berbasis himpunan daripada pemrosesan baris demi baris.

• Batasi Jumlah Kolom dalam Subquery: Pilih hanya kolom yang Anda perlukan dalam subquery untuk meminimalkan pengambilan data, mengurangi penggunaan memori, dan memungkinkan basis data mengoptimalkan eksekusi.

• Gunakan EXISTS alih-alih IN: Jika subquery mengembalikan dataset besar, menggunakan EXISTS daripada IN dapat meningkatkan kinerja. Operator EXISTS akan menghentikan pemrosesan kueri saat menemukan baris yang cocok, sedangkan operator IN terus mengevaluasi seluruh hasil subquery.

Menghindari jebakan umum

Saat Anda menulis subquery secara tidak tepat, Anda dapat mengalami masalah saat mengeksekusinya. Mari kita lihat cara menghindari jebakan ini.

• Hindari Subquery Berkorelasi yang Tidak Perlu: Subquery berkorelasi memakan banyak sumber daya, jadi hindari menggunakannya ketika hasilnya dapat dicapai dengan subquery tidak berkorelasi atau join.

• Perhatikan Nilai NULL dalam Subquery: Nilai NULL dapat menghasilkan hasil yang tidak terduga, terutama dalam subquery yang menggunakan operator perbandingan seperti IN atau =. Untuk menghindari kesalahan, pertimbangkan menggunakan COALESCE untuk menangani null atau memastikan kolom tidak dapat bernilai null jika digunakan untuk perbandingan.

• Hindari Menggunakan SELECT * dalam Subquery: Penggunaan SELECT * dapat menyebabkan inefisiensi, karena mengambil semua kolom meskipun tidak dibutuhkan. Ini meningkatkan penggunaan memori dan dapat memperlambat eksekusi kueri, terutama pada dataset besar.

• Gunakan Alias yang Bermakna: Beri nama tabel dan subquery Anda dengan jelas untuk meningkatkan keterbacaan.

Coba jalur karier SQL Server Developer kami, yang akan membekali Anda dengan keterampilan untuk menulis, menelusuri masalah, dan mengoptimalkan kueri menggunakan SQL Server. 

Teknik Subquery SQL yang Lebih Lanjut

Walaupun subquery menawarkan cara efisien untuk menulis kueri kompleks, ada metode tingkat lanjut untuk menangani data hierarkis. Mari kita lihat teknik dan strategi lanjutan dalam menerapkan subquery SQL.

Subquery rekursif

Subquery rekursif (dikenal juga sebagai recursive common table expressions atau CTE) memungkinkan Anda mengambil data hierarkis, seperti struktur organisasi, kategori produk, atau relasi berbasis graf, di mana setiap item dalam data terhubung dengan item lainnya.

Misalkan Anda memiliki tabel employees dengan employee_id, manager_id, dan employee_name. Anda ingin mengambil hierarki karyawan di bawah manajer tertentu.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Query: Start with the specified manager
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
 -- Assuming the top-level manager has NULL as manager_id
    WHERE manager_id IS NULL 
    
    UNION ALL
    
    -- Recursive Query: Find employees who report to those in the previous level
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Pada kueri di atas:

• Yang saya sebut Anchor Query memilih manajer level teratas (di mana manager_id adalah NULL).

• Recursive Query melakukan join employees dengan CTE itu sendiri (EmployeeHierarchy), menemukan karyawan yang melapor ke setiap karyawan yang sebelumnya diambil.

• Rekursi berlanjut hingga tidak ada lagi karyawan yang melapor pada yang telah ditemukan.

Menggabungkan subquery dengan fitur SQL lain

Anda dapat mengintegrasikan subquery dengan fitur SQL lain seperti window functions, pernyataan CASE, dan fungsi pengelompokan. Kombinasi ini memungkinkan manipulasi data tingkat lanjut dan pelaporan yang lebih komprehensif.

Menggabungkan subquery dengan window functions

Subquery dapat digunakan untuk memperkaya himpunan data yang dikenai window functions, sehingga berguna untuk pemeringkatan, total kumulatif, dan rata-rata berjalan. Misalkan Anda ingin memberi peringkat produk berdasarkan penjualan di tiap wilayah. Anda dapat menggunakan subquery untuk memilih data yang relevan lalu menerapkan window function untuk peringkat.

 -- Ranks products by sales within each region
SELECT region, product_id, sales, 
       RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank 
-- Subquery: calculates total sales per product in each region
FROM (SELECT region, product_id, SUM(sales) AS sales  
      FROM sales_data
      GROUP BY region, product_id) AS regional_sales;

Menggunakan subquery dengan pernyataan CASE

Menggabungkan subquery dengan pernyataan CASE dapat membantu Anda menerapkan kondisi kompleks berdasarkan perhitungan dinamis. Kueri berikut mengklasifikasikan produk sebagai “High”, “Medium”, atau “Low” berdasarkan penjualannya relatif terhadap penjualan rata-rata untuk kategorinya.

 -- Categorize above-average sales, average sales, and below-average sales
SELECT product_id, category_id, sales,
       CASE 
           WHEN sales > (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'High' 
           WHEN sales = (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'Medium' 
           ELSE 'Low'
       END AS performance
FROM products AS p;

Subquery dengan fungsi agregat untuk agregasi kondisional

Anda juga dapat menghitung agregat kondisional menggunakan subquery di dalam fungsi agregat. Misalkan Anda ingin menghitung total pendapatan yang dihasilkan hanya oleh pelanggan aktif. Pada contoh di bawah, subquery mengambil semua pelanggan aktif. Kueri utama kemudian memfilter pesanan untuk hanya menyertakan pesanan dari pelanggan aktif, menghitung total pendapatan dari grup ini.

 -- Calculates total revenue from active customers
SELECT SUM(order_total) AS active_customer_revenue 
FROM orders
  -- Subquery: retrieves IDs of active customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'Active'
);

Kapan Subquery SQL Digunakan

Subquery SQL menawarkan penerapan dunia nyata yang serbaguna untuk analisis data, itulah mengapa subquery sering digunakan. Berikut adalah beberapa penerapan spesifik industri atau matematis dari subquery SQL. Untuk mendapatkan ide-ide ini, saya mencoba memikirkan pekerjaan analisis atau rekayasa data yang memerlukan beberapa langkah, yang menurut saya adalah wilayah di mana subquery sangat membantu.

Contoh spesifik industri

Subquery dapat menawarkan solusi bermanfaat di industri keuangan, kesehatan, dan ritel. Berikut beberapa gagasan:

• Penilaian Risiko untuk Persetujuan Pinjaman (Keuangan): Saya membayangkan bank menyeimbangkan metrik seperti rasio utang terhadap pendapatan dan skor kredit. Dengan menyarangkan metrik ini dalam subquery, analis dapat lebih memahami metrik keuangan yang rumit. Mungkin, sebuah subquery dapat menghitung jumlah pinjaman rata-rata untuk pelanggan dalam kelompok pendapatan tertentu.

• Mengidentifikasi Pola dalam Diagnosis Pasien (Kesehatan): Di bidang kesehatan, mengelola data pasien bisa menjadi pekerjaan besar. Subquery dapat membantu memecah kompleksitas ini. Saya membayangkan menggunakan subquery untuk melacak frekuensi diagnosis di berbagai kelompok usia atau faktor risiko.

• Mengoptimalkan Penataan Produk Berdasarkan Riwayat Pembelian (Ritel): Peritel sangat bergantung pada pemahaman pola pembelian. Subquery dapat menampilkan data pembelian bersarang untuk mengidentifikasi produk yang sering dibeli bersamaan. Ini dapat membantu menata barang pelengkap secara strategis dan meningkatkan penjualan.

Keterkaitan matematis

Subquery juga digunakan untuk mengidentifikasi pola dan tren data dalam keterkaitan matematis dan logis. Berikut beberapa skenario di mana subquery diterapkan dalam matematika.

• Rata-rata Bergerak untuk Analisis Deret Waktu: Saat menganalisis tren dari waktu ke waktu, subquery menyederhanakan perhitungan rata-rata bergerak. Saya melihat subquery mendefinisikan jendela waktu tertentu dalam kueri bersarang, sehingga lebih mudah menghaluskan data dan melihat tren.

• Mendeteksi Pencilan Menggunakan Simpangan Baku: Menemukan pencilan itu penting untuk banyak hal, termasuk deteksi penipuan. Subquery memudahkan penghitungan metrik terhitung seperti simpangan baku dalam kueri bersarang.

• Menggunakan Konsep Teori Himpunan: Menarik bagi saya bagaimana subquery mencerminkan operasi teori himpunan seperti UNION dan INTERSECT. Kemampuan ini cocok untuk tugas seperti analisis retensi pelanggan, di mana memahami irisan dan perbedaan antar kelompok pelanggan dapat mendorong strategi pemasaran yang lebih cerdas.

Kesimpulan

Menguasai subquery SQL dapat secara signifikan meningkatkan kemampuan Anda dalam mengelola dan menganalisis data secara efisien. Dengan memahami strukturnya, penerapannya, dan praktik terbaiknya, Anda dapat mengoptimalkan kueri SQL untuk kinerja yang lebih baik. Selain itu, saya ingin menekankan bahwa menguasai subquery benar-benar membuat penulisan SQL menjadi lebih mudah, jadi layak untuk dipelajari.

Jika Anda tertarik menjadi analis data yang andal, lihat jalur karier Associate Data Analyst in SQL untuk mempelajari keterampilan yang diperlukan. Kursus Reporting in SQL juga tepat jika Anda ingin belajar membuat dasbor profesional menggunakan SQL. Terakhir, saya merekomendasikan memperoleh SQL Associate Certification untuk menunjukkan kemahiran Anda menggunakan SQL untuk analisis data dan menonjol di antara profesional data lainnya.