1. Memahami Relasi Antar Tabel: SQL JOIN
Dalam basis data relasional (RDBMS), data dinormalisasi dan dipecah ke berbagai tabel untuk menghindari duplikasi. Ketika data dibutuhkan secara utuh, kita menyatukannya kembali menggunakan operasi **JOIN** berdasarkan kunci referensial (*Foreign Key*).
2. Jenis-jenis SQL JOIN
- **INNER JOIN**: Mengembalikan baris hanya jika ada kecocokan di *kedua* tabel. - **LEFT JOIN**: Mengembalikan *semua* baris dari tabel kiri, ditambah data yang cocok dari tabel kanan (atau NULL jika tidak cocok). - **RIGHT JOIN**: Mengembalikan *semua* baris dari tabel kanan, ditambah data yang cocok dari tabel kiri.
query.sql
sql
| 1 | -- Mengambil daftar order beserta nama lengkap usernya (INNER JOIN) |
| 2 | SELECT |
| 3 | orders.id AS order_id, |
| 4 | users.full_name, |
| 5 | orders.total_amount, |
| 6 | orders.status |
| 7 | FROM orders |
| 8 | INNER JOIN users ON orders.user_id = users.id; |
| 9 | |
| 10 | -- Mengambil SEMUA user, termasuk yang belum pernah berbelanja (LEFT JOIN) |
| 11 | SELECT |
| 12 | users.full_name, |
| 13 | COUNT(orders.id) AS total_orders |
| 14 | FROM users |
| 15 | LEFT JOIN orders ON users.id = orders.user_id |
| 16 | GROUP BY users.id, users.full_name; |
3. Fungsi Agregasi & GROUP BY
Fungsi agregasi seperti `COUNT()`, `SUM()`, `AVG()`, `MIN()`, dan `MAX()` digunakan untuk menghitung nilai rangkuman dari kumpulan baris data.
query.sql
sql
| 1 | -- Menghitung total omzet per bulan dengan status 'paid' |
| 2 | SELECT |
| 3 | DATE_TRUNC('month', created_at) AS order_month, |
| 4 | COUNT(id) AS number_of_sales, |
| 5 | SUM(total_amount) AS total_revenue, |
| 6 | AVG(total_amount) AS average_order_value |
| 7 | FROM orders |
| 8 | WHERE status = 'paid' |
| 9 | GROUP BY order_month |
| 10 | HAVING SUM(total_amount) > 10000000 |
| 11 | ORDER BY order_month DESC; |
Perbedaan WHERE vs HAVING
`WHERE` memfilter baris individual *sebelum* pengelompokan GROUP BY dijalankan. Sedangkan `HAVING` memfilter hasil perhitungan agregat *setelah* pengelompokan selesai dihitung.
4. Simulasi Inner Join di Terminal
example.js
javascript
| 1 | const users = [ |
| 2 | { id: 101, name: "Rina" }, |
| 3 | { id: 102, name: "Dimas" }, |
| 4 | { id: 103, name: "Citra" } |
| 5 | ]; |
| 6 | |
| 7 | const courses = [ |
| 8 | { id: 1, userId: 101, title: "Programming Basics" }, |
| 9 | { id: 2, userId: 101, title: "Web Fundamentals" }, |
| 10 | { id: 3, userId: 102, title: "JavaScript Mastery" } |
| 11 | ]; |
| 12 | |
| 13 | function innerJoin(leftTable, rightTable, leftKey, rightKey) { |
| 14 | const results = []; |
| 15 | for (const leftRow of leftTable) { |
| 16 | for (const rightRow of rightTable) { |
| 17 | if (leftRow[leftKey] === rightRow[rightKey]) { |
| 18 | results.push({ ...leftRow, ...rightRow }); |
| 19 | } |
| 20 | } |
| 21 | } |
| 22 | return results; |
| 23 | } |
| 24 | |
| 25 | console.log("=== HASIL INNER JOIN USERS & COURSES ==="); |
| 26 | console.log(innerJoin(courses, users, "userId", "id")); |