Basic Joins: Menggabungkan Data dari Beberapa Tabel

Halo SQL Explorers! Di materi sebelumnya kita udah belajar filter data. Sekarang kita bakal belajar cara gabungin data dari beberapa tabel pake JOIN!

Kenapa Perlu JOIN?

Di dunia nyata, data biasanya tersebar di beberapa tabel:

  • Tabel customers: Data pelanggan (id, nama, email)
  • Tabel orders: Data pesanan (id, customer_id, tanggal)
  • Tabel order_items: Detail item yang dipesan (order_id, product_id, quantity)
  • Tabel products: Data produk (id, nama, harga)

Kita perlu JOIN buat:

  • Liat detail pesanan customer
  • Analisis penjualan produk
  • Bikin laporan yang lengkap

Jenis-jenis JOIN

1. INNER JOIN

Cuma ambil data yang cocok di kedua tabel:

-- Liat pesanan dengan detail customernya
SELECT 
    c.nama as customer,
    o.tanggal,
    o.id as order_id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Hasilnya:
+----------+------------+----------+
| customer | tanggal    | order_id |
+----------+------------+----------+
| Budi     | 2024-01-01 |        1 |
| Ani      | 2024-01-02 |        2 |
| Caca     | 2024-01-03 |        3 |
+----------+------------+----------+

2. LEFT JOIN

Ambil semua data dari tabel kiri + data yang cocok dari tabel kanan:

-- Liat semua customer, termasuk yang belum pernah order
SELECT 
    c.nama as customer,
    COUNT(o.id) as total_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.nama;

-- Hasilnya:
+----------+-------------+
| customer | total_order |
+----------+-------------+
| Budi     |           2 |
| Ani      |           1 |
| Caca     |           1 |
| Dodi     |           0 |
+----------+-------------+

3. RIGHT JOIN

Ambil semua data dari tabel kanan + data yang cocok dari tabel kiri:

-- Liat semua order, termasuk yang customernya udah dihapus
SELECT 
    o.id as order_id,
    c.nama as customer
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- Hasilnya:
+----------+----------+
| order_id | customer |
+----------+----------+
|        1 | Budi     |
|        2 | Ani      |
|        3 | NULL     |
+----------+----------+

4. FULL OUTER JOIN

Ambil semua data dari kedua tabel (MySQL pake UNION):

-- Liat semua customer dan order
SELECT 
    c.nama as customer,
    o.id as order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT 
    c.nama as customer,
    o.id as order_id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- Hasilnya:
+----------+----------+
| customer | order_id |
+----------+----------+
| Budi     |        1 |
| Ani      |        2 |
| Caca     |        3 |
| Dodi     |     NULL |
| NULL     |        4 |
+----------+----------+

JOIN Multiple Tables

Kita bisa JOIN lebih dari 2 tabel:

-- Liat detail pesanan lengkap
SELECT 
    c.nama as customer,
    o.tanggal,
    p.nama as product,
    oi.quantity,
    oi.price as harga_satuan,
    (oi.quantity * oi.price) as total
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;

-- Hasilnya:
+----------+------------+-----------------+----------+--------------+---------+
| customer | tanggal    | product         | quantity | harga_satuan | total   |
+----------+------------+-----------------+----------+--------------+---------+
| Budi     | 2024-01-01 | Laptop Gaming  |        1 |    15000000  | 15000000|
| Ani      | 2024-01-02 | Smartphone     |        2 |     5000000  | 10000000|
| Caca     | 2024-01-03 | Kemeja Casual  |        3 |      200000  |   600000|
+----------+------------+-----------------+----------+--------------+---------+

Tips JOIN yang Efektif

  1. Pilih JOIN yang Tepat

    -- Pake LEFT JOIN kalo mau liat semua customer
SELECT c.nama, COUNT(o.id) as total_order
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.nama;

-- Pake INNER JOIN kalo cuma mau liat yang match
SELECT c.nama, COUNT(o.id) as total_order
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.nama;

  2. Alias yang Jelas

    -- Bikin query lebih mudah dibaca
SELECT 
    cust.nama as customer,
    ord.tanggal as order_date,
    prod.nama as product
FROM customers cust
JOIN orders ord ON cust.id = ord.customer_id
JOIN products prod ON ord.product_id = prod.id;

  3. Filter Setelah JOIN

    -- Cari pesanan di atas 1 juta
SELECT 
    c.nama as customer,
    o.tanggal,
    SUM(oi.quantity * oi.price) as total
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.nama, o.tanggal
HAVING total > 1000000;

Latihan JOIN

  1. Cari Customer VIP
-- Customer dengan total belanja > 10 juta
SELECT 
    c.nama as customer,
    COUNT(DISTINCT o.id) as total_order,
    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
GROUP BY c.nama
HAVING total_belanja > 10000000;
  1. Analisis Produk
-- Produk yang belum pernah dibeli
SELECT p.nama as product
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

Preview Materi Selanjutnya

Di materi advanced joins kita bakal belajar:

  • SELF JOIN
  • CROSS JOIN
  • JOIN dengan subquery
  • Dan masih banyak lagi!

Siap belajar JOIN yang lebih kompleks? Yuk lanjut ke materi advanced joins!

Fatkul Nur Koirudin