Subqueries: Query di Dalam Query 📦

Halo SQL Explorers! 👋 Di materi sebelumnya kita udah belajar JOIN yang kompleks. Sekarang kita bakal belajar subquery, cara keren buat bikin query yang lebih canggih!

Apa itu Subquery? 🤔

Subquery itu “query di dalam query”. Bayangkan:

  • Kamu mau tau produk yang harganya di atas rata-rata
  • Kamu perlu cari rata-rata dulu, baru filter produknya
  • Ini bisa dipake buat analisis yang lebih kompleks!

Data yang Kita Pake 📊

Kita tetep pake database toko online kita, tapi kita bakal liat dari sudut pandang yang beda:

-- Liat rata-rata pembelian per customer
SELECT 
    c.nama,
    (SELECT AVG(oi.quantity * oi.price)
     FROM orders o
     JOIN order_items oi ON o.id = oi.order_id
     WHERE o.customer_id = c.id) as rata_rata_belanja
FROM customers c;

-- Hasilnya:
+------+------------------+
| nama | rata_rata_belanja|
+------+------------------+
| Budi |         10000000 |
| Ani  |          275000  |
| Caca |           50000  |
| Dodi |            NULL  |
+------+------------------+

Jenis-jenis Subquery 🔄

1. Scalar Subquery

Subquery yang ngasih SATU nilai:

-- Cari produk yang harganya di atas rata-rata
SELECT nama, harga
FROM products
WHERE harga > (
    SELECT AVG(harga) 
    FROM products
);

-- Hitung persentase harga dari total
SELECT 
    nama,
    harga,
    (harga / (SELECT SUM(harga) FROM products) * 100) as persen_total
FROM products;

2. List Subquery

Subquery yang ngasih BANYAK nilai:

-- Cari produk yang pernah dibeli
SELECT nama
FROM products
WHERE id IN (
    SELECT DISTINCT product_id 
    FROM order_items
);

-- Cari customer yang belum pernah order
SELECT nama
FROM customers
WHERE id NOT IN (
    SELECT DISTINCT customer_id 
    FROM orders
);

3. Table Subquery

Subquery yang ngasih TABEL sementara:

-- Analisis penjualan per kategori
SELECT 
    cat.nama as kategori,
    sales.total_penjualan
FROM categories cat
JOIN (
    SELECT 
        p.category_id,
        SUM(oi.quantity * oi.price) as total_penjualan
    FROM products p
    JOIN order_items oi ON p.id = oi.product_id
    GROUP BY p.category_id
) sales ON cat.id = sales.category_id;

4. Correlated Subquery

Subquery yang TERHUBUNG ke query utama:

-- Cari produk termahal di tiap kategori
SELECT p1.nama, p1.category_id, p1.harga
FROM products p1
WHERE p1.harga = (
    SELECT MAX(p2.harga)
    FROM products p2
    WHERE p2.category_id = p1.category_id
);

-- Cari customer dengan order di atas rata-rata
SELECT c.nama
FROM customers c
WHERE (
    SELECT AVG(oi.quantity * oi.price)
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.customer_id = c.id
) > (
    SELECT AVG(oi.quantity * oi.price)
    FROM order_items oi
);

Tips Pake Subquery 💡

  1. Pilih yang Tepat: JOIN vs Subquery

    -- Pake JOIN kalo perlu data dari tabel lain
SELECT c.nama, o.tanggal
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- Pake subquery kalo perlu perhitungan
SELECT nama,
       (SELECT COUNT(*) 
        FROM orders 
        WHERE customer_id = c.id) as total_order
FROM customers c;

  2. Perhatiin Performa

    -- Subquery yang berat
SELECT *
FROM products
WHERE category_id IN (
    SELECT id 
    FROM categories 
    WHERE nama LIKE '%Electronics%'
);

-- Lebih cepet pake JOIN
SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.nama LIKE '%Electronics%';

  3. Pake Alias yang Jelas

    -- Bikin query lebih mudah dibaca
SELECT p1.nama,
       (SELECT MAX(p2.harga)
        FROM products p2
        WHERE p2.category_id = p1.category_id) as max_harga
FROM products p1;

Latihan Subquery 🎯

  1. Analisis Customer Premium
-- Cari customer yang total belanjanya di atas rata-rata
SELECT 
    c.nama,
    (SELECT SUM(oi.quantity * oi.price)
     FROM orders o
     JOIN order_items oi ON o.id = oi.order_id
     WHERE o.customer_id = c.id) as total_belanja
FROM customers c
HAVING total_belanja > (
    SELECT AVG(subtotal)
    FROM (
        SELECT o.customer_id,
               SUM(oi.quantity * oi.price) as subtotal
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        GROUP BY o.customer_id
    ) avg_spending
);
  1. Produk Terlaris per Kategori
-- Cari produk dengan penjualan tertinggi di tiap kategori
SELECT 
    c.nama as kategori,
    p.nama as produk,
    sales.total_terjual
FROM products p
JOIN categories c ON p.category_id = c.id
JOIN (
    SELECT 
        product_id,
        SUM(quantity) as total_terjual
    FROM order_items
    GROUP BY product_id
) sales ON p.id = sales.product_id
WHERE (p.category_id, sales.total_terjual) IN (
    SELECT 
        p2.category_id,
        MAX(oi2.quantity)
    FROM products p2
    JOIN order_items oi2 ON p2.id = oi2.product_id
    GROUP BY p2.category_id
);

Preview Materi Selanjutnya 🎯

Di materi aggregate functions kita bakal belajar:

  • Fungsi COUNT, SUM, AVG
  • Grouping data yang kompleks
  • Window functions
  • Dan masih banyak lagi! 🚀

Siap belajar lebih dalam? Yuk lanjut ke materi aggregate functions! 🎯

Fatkul Nur Koirudin