Advanced Joins: JOIN SQL Tingkat Lanjut 🔄

Halo SQL Explorers! 👋 Di materi sebelumnya kita udah belajar JOIN dasar. Sekarang kita bakal belajar teknik JOIN yang lebih canggih!

Review JOIN Dasar 🤔

Di materi basic, kita udah belajar:

INNER JOIN: Ambil data yang cocok di kedua tabel
LEFT JOIN: Ambil semua data dari tabel kiri
RIGHT JOIN: Ambil semua data dari tabel kanan
Multiple JOIN: Gabung lebih dari 2 tabel

Evolusi Database Kita 📊

Di level basic, kita udah punya tabel:

customers: Data pelanggan
products: Data produk
categories: Kategori produk
orders: Data pesanan

Sekarang kita bakal nambahin satu tabel penting: order_items!

Kenapa Perlu Tabel Order Items? 🤔

Di basic SQL, kita belum bisa:

Tau detail item apa aja yang dibeli dalam satu order
Hitung total belanja per order
Analisis produk mana yang paling laku

Makanya kita perlu tabel order_items:

-- Bikin tabel order_items
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(15,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Isi data order_items
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
    (1, 1, 1, 15000000),  -- Budi beli Laptop Gaming
    (2, 2, 1, 5000000),   -- Budi beli Smartphone
    (3, 3, 2, 200000),    -- Ani beli 2 Kemeja Casual
    (3, 4, 1, 350000),    -- Ani beli Celana Jeans
    (4, 5, 5, 50000);     -- Caca beli 5 Snack Pack

1. Multiple JOIN 🔄

Contoh 1: Detail Pembelian Lengkap

-- Tampilkan detail lengkap setiap pembelian
SELECT 
    c.nama AS customer,
    p.nama AS produk,
    cat.nama AS kategori,
    oi.quantity AS jumlah,
    oi.price AS harga,
    (oi.quantity * oi.price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id;

-- Hasilnya:
+----------+---------------+-----------+--------+----------+----------+
| customer | produk        | kategori  | jumlah | harga    | total    |
+----------+---------------+-----------+--------+----------+----------+
| Budi     | Laptop Gaming | Elektronik|      1 | 15000000 | 15000000 |
| Budi     | Smartphone    | Elektronik|      1 |  5000000 |  5000000 |
| Ani      | Kemeja Casual | Fashion   |      2 |   200000 |   400000 |
| Ani      | Celana Jeans  | Fashion   |      1 |   350000 |   350000 |
| Caca     | Snack Pack    | Makanan   |      5 |    50000 |   250000 |
+----------+---------------+-----------+--------+----------+----------+

Contoh 2: Analisis Penjualan per Kategori

-- Hitung total penjualan per kategori
SELECT 
    cat.nama AS kategori,
    COUNT(DISTINCT o.id) AS jumlah_order,
    SUM(oi.quantity) AS total_item,
    SUM(oi.quantity * oi.price) AS total_penjualan
FROM categories cat
JOIN products p ON cat.id = p.category_id
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
GROUP BY cat.nama;

-- Hasilnya:
+-----------+--------------+------------+-----------------+
| kategori  | jumlah_order| total_item | total_penjualan |
+-----------+--------------+------------+-----------------+
| Elektronik|            2|          2 |        20000000 |
| Fashion   |            1|          3 |          750000 |
| Makanan   |            1|          5 |          250000 |
+-----------+--------------+------------+-----------------+

Contoh 3: Riwayat Belanja Customer

-- Tampilkan riwayat belanja per customer
SELECT 
    c.nama AS customer,
    COUNT(DISTINCT o.id) AS total_order,
    GROUP_CONCAT(DISTINCT cat.nama) AS kategori_dibeli,
    SUM(oi.quantity * oi.price) AS total_belanja
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
GROUP BY c.nama;

-- Hasilnya:
+----------+-------------+------------------+--------------+
| customer | total_order | kategori_dibeli  | total_belanja|
+----------+-------------+------------------+--------------+
| Budi     |           2 | Elektronik       |     20000000 |
| Ani      |           1 | Fashion          |       750000 |
| Caca     |           1 | Makanan          |       250000 |
+----------+-------------+------------------+--------------+

2. SELF JOIN 🔄

Contoh 1: Produk dengan Harga Mirip

-- Cari produk dengan selisih harga <= 100000
SELECT 
    p1.nama as produk1,
    p1.harga as harga1,
    p2.nama as produk2,
    p2.harga as harga2,
    ABS(p1.harga - p2.harga) as selisih_harga
FROM products p1
JOIN products p2 ON p1.id < p2.id
WHERE ABS(p1.harga - p2.harga) <= 100000;

-- Hasilnya:
+--------------+---------+--------------+---------+---------------+
| produk1      | harga1  | produk2      | harga2  | selisih_harga |
+--------------+---------+--------------+---------+---------------+
| Kemeja Casual|  200000 | Celana Jeans |  250000 |         50000 |
| Snack Pack   |   50000 | Minuman Soda |   45000 |          5000 |
+--------------+---------+--------------+---------+---------------+

Contoh 2: Rekomendasi Produk Kategori Sama

-- Tampilkan rekomendasi produk dalam kategori yang sama
SELECT 
    p1.nama as produk,
    p1.harga as harga_produk,
    p2.nama as rekomendasi,
    p2.harga as harga_rekomendasi,
    c.nama as kategori
FROM products p1
JOIN products p2 
    ON p1.category_id = p2.category_id 
    AND p1.id != p2.id
JOIN categories c ON p1.category_id = c.id;

-- Hasilnya:
+--------------+--------------+-------------+------------------+-----------+
| produk       | harga_produk | rekomendasi | harga_rekomendasi| kategori  |
+--------------+--------------+-------------+------------------+-----------+
| Laptop Gaming|     15000000 | Smartphone  |          5000000 | Elektronik|
| Smartphone   |      5000000 | Laptop Gaming|        15000000 | Elektronik|
| Kemeja Casual|       200000 | Celana Jeans|          250000 | Fashion   |
+--------------+--------------+-------------+------------------+-----------+

Contoh 3: Perbandingan Harga dalam Kategori

-- Bandingkan harga produk dengan rata-rata kategorinya
SELECT 
    p1.nama as produk,
    c.nama as kategori,
    p1.harga as harga,
    ROUND(AVG(p2.harga), 0) as rata_rata_kategori,
    CASE 
        WHEN p1.harga > AVG(p2.harga) THEN 'Di Atas Rata-rata'
        WHEN p1.harga < AVG(p2.harga) THEN 'Di Bawah Rata-rata'
        ELSE 'Rata-rata'
    END as status_harga
FROM products p1
JOIN products p2 
    ON p1.category_id = p2.category_id
JOIN categories c ON p1.category_id = c.id
GROUP BY p1.nama, c.nama, p1.harga;

-- Hasilnya:
+--------------+-----------+----------+------------------+------------------+
| produk       | kategori  | harga    | rata_rata_kategori| status_harga     |
+--------------+-----------+----------+------------------+------------------+
| Laptop Gaming| Elektronik| 15000000 |          10000000| Di Atas Rata-rata|
| Smartphone   | Elektronik|  5000000 |          10000000| Di Bawah Rata-rata|
| Kemeja Casual| Fashion   |   200000 |            225000| Di Bawah Rata-rata|
+--------------+-----------+----------+------------------+------------------+

3. CROSS JOIN 🔄

Contoh 1: Bundle Produk Beda Kategori

-- Buat kombinasi bundle produk antar kategori
SELECT 
    p1.nama as produk1,
    c1.nama as kategori1,
    p2.nama as produk2,
    c2.nama as kategori2,
    (p1.harga + p2.harga) * 0.9 as harga_bundle
FROM products p1
JOIN categories c1 ON p1.category_id = c1.id
CROSS JOIN products p2
JOIN categories c2 ON p2.category_id = c2.id
WHERE 
    p1.id < p2.id
    AND c1.id != c2.id;

-- Hasilnya:
+--------------+-----------+--------------+-----------+--------------+
| produk1      | kategori1 | produk2      | kategori2 | harga_bundle |
+--------------+-----------+--------------+-----------+--------------+
| Laptop Gaming| Elektronik| Kemeja Casual| Fashion   |     13680000 |
| Laptop Gaming| Elektronik| Snack Pack   | Makanan   |     13545000 |
| Smartphone   | Elektronik| Celana Jeans | Fashion   |      4725000 |
+--------------+-----------+--------------+-----------+--------------+

Contoh 2: Matriks Kategori dan Customer

-- Analisis pembelian customer per kategori
SELECT 
    c.nama as customer,
    cat.nama as kategori,
    COALESCE(COUNT(DISTINCT o.id), 0) as jumlah_order,
    COALESCE(SUM(oi.quantity), 0) as total_item
FROM customers c
CROSS JOIN categories cat
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id AND p.category_id = cat.id
GROUP BY c.nama, cat.nama
ORDER BY c.nama, cat.nama;

-- Hasilnya:
+----------+-----------+--------------+------------+
| customer | kategori  | jumlah_order | total_item |
+----------+-----------+--------------+------------+
| Ani      | Elektronik|            0 |          0 |
| Ani      | Fashion   |            1 |          3 |
| Ani      | Makanan   |            0 |          0 |
| Budi     | Elektronik|            2 |          2 |
| Budi     | Fashion   |            0 |          0 |
| Budi     | Makanan   |            0 |          0 |
+----------+-----------+--------------+------------+

Contoh 3: Analisis Potensi Cross-Selling

-- Analisis kombinasi pembelian antar kategori
SELECT 
    c1.nama as kategori1,
    c2.nama as kategori2,
    COUNT(DISTINCT o.id) as order_bersama,
    COUNT(DISTINCT cust.id) as jumlah_customer
FROM categories c1
CROSS JOIN categories c2
LEFT JOIN products p1 ON c1.id = p1.category_id
LEFT JOIN products p2 ON c2.id = p2.category_id
LEFT JOIN order_items oi1 ON p1.id = oi1.product_id
LEFT JOIN order_items oi2 ON p2.id = oi2.product_id
LEFT JOIN orders o ON oi1.order_id = o.id AND oi1.order_id = oi2.order_id
LEFT JOIN customers cust ON o.customer_id = cust.id
WHERE c1.id < c2.id
GROUP BY c1.nama, c2.nama;

-- Hasilnya:
+-----------+-----------+---------------+----------------+
| kategori1 | kategori2 | order_bersama | jumlah_customer|
+-----------+-----------+---------------+----------------+
| Elektronik| Fashion   |             0 |              0 |
| Elektronik| Makanan   |             0 |              0 |
| Fashion   | Makanan   |             1 |              1 |
+-----------+-----------+---------------+----------------+

4. JOIN dengan Subquery 📦

Contoh 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
);

-- Hasilnya:
+------------+--------------+---------------+
| kategori   | produk       | total_terjual |
+------------+--------------+---------------+
| Elektronik | Smartphone   |            25 |
| Fashion    | Kemeja Casual|            15 |
| Makanan    | Snack Pack   |            50 |
+------------+--------------+---------------+

Contoh 2: Customer dengan Total Belanja Tertinggi

-- Analisis peringkat customer berdasarkan total belanja
WITH customer_totals AS (
    SELECT 
        c.id,
        c.nama,
        COUNT(DISTINCT o.id) as jumlah_order,
        SUM(oi.quantity * oi.price) as total_belanja,
        RANK() OVER (ORDER BY SUM(oi.quantity * oi.price) DESC) as ranking
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items oi ON o.id = oi.order_id
    GROUP BY c.id, c.nama
)
SELECT 
    ct.*,
    CASE 
        WHEN ranking = 1 THEN 'Top Spender'
        WHEN ranking <= 3 THEN 'High Value'
        ELSE 'Regular'
    END as segment
FROM customer_totals ct;

-- Hasilnya:
+----+----------+--------------+---------------+---------+-------------+
| id | nama     | jumlah_order | total_belanja | ranking | segment     |
+----+----------+--------------+---------------+---------+-------------+
| 1  | Budi     |            2 |      20000000 |       1 | Top Spender |
| 2  | Ani      |            1 |        750000 |       2 | High Value  |
| 3  | Caca     |            1 |        250000 |       3 | High Value  |
+----+----------+--------------+---------------+---------+-------------+

Contoh 3: Analisis Kategori Favorit Customer

-- Identifikasi kategori favorit tiap customer
WITH category_purchases AS (
    SELECT 
        c.nama as customer,
        cat.nama as kategori,
        COUNT(DISTINCT o.id) as jumlah_order,
        SUM(oi.quantity) as total_item,
        RANK() OVER (PARTITION BY c.nama ORDER BY COUNT(o.id) DESC) as kategori_rank
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    JOIN categories cat ON p.category_id = cat.id
    GROUP BY c.nama, cat.nama
)
SELECT 
    customer,
    kategori as kategori_favorit,
    jumlah_order,
    total_item
FROM category_purchases
WHERE kategori_rank = 1;

-- Hasilnya:
+----------+------------------+--------------+------------+
| customer | kategori_favorit | jumlah_order | total_item |
+----------+------------------+--------------+------------+
| Budi     | Elektronik      |            2 |          2 |
| Ani      | Fashion         |            1 |          3 |
| Caca     | Makanan         |            1 |          5 |
+----------+------------------+--------------+------------+

Tips JOIN Lanjutan 💡

Optimalkan Query dengan Index

-- Bikin index buat kolom yang sering di-JOIN
CREATE INDEX idx_product_category 
ON products(category_id);

CREATE INDEX idx_order_items_order 
ON order_items(order_id);

Gunakan Alias yang Jelas

-- Bikin query lebih mudah dibaca
SELECT 
    prod.nama as product_name,
    cat.nama as category_name,
    sales.total_sales
FROM products prod
JOIN categories cat ON prod.category_id = cat.id
JOIN (
    SELECT product_id, SUM(quantity) as total_sales
    FROM order_items
    GROUP BY product_id
) sales ON prod.id = sales.product_id;

Handle NULL Values

-- Pake COALESCE buat handle NULL
SELECT 
    c.nama as customer,
    COALESCE(COUNT(o.id), 0) as total_orders,
    COALESCE(SUM(oi.quantity * oi.price), 0) as total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.nama;

Preview Materi Selanjutnya 🎯

Di materi subqueries kita bakal belajar:

Query di dalam query
Subquery di WHERE
Subquery di FROM
Dan masih banyak lagi! 🚀

Siap belajar subquery? Yuk lanjut ke materi subqueries! 🎯