Penggabungan Tabel (3) — Menumpuk WHERE / ORDER BY / CASE di atas JOIN

Data yang akan kita pakai — department, employee, dan sales

Di artikel-artikel sebelumnya kamu sudah melihat berbagai variasi join — INNER JOIN, OUTER JOIN, dan self-join.

Di artikel ini kamu akan menumpuk filter WHERE, ORDER BY, dan label band CASE di atas JOIN dan membangun bentuk laporan agregasi yang benar-benar akan kamu temui di pekerjaan nyata.

Datanya adalah department (6 departemen) dan employee (30 karyawan), ditambah tabel baru sales (50 baris) berisi item penjualan.

sales.emp_id adalah foreign key yang menunjuk ke employee.emp_id.

Kita akan menggabungkan ketiga tabel untuk membuat laporan yang mencantumkan nama departemen dan nama sales di samping setiap penjualan.

Sebelum masuk ke latihan, mari pastikan definisi kolom dan sampel baris dari ketiga tabel — department, employee, dan sales.

① Jalankan PRAGMA table_info(table_name); pada tiap tabel untuk memeriksa kolomnya.

② Jalankan SELECT * FROM table_name LIMIT 5; pada tiap tabel untuk melihat 5 baris pertamanya. Perhatikan bagaimana sales.emp_id menunjuk ke baris di employee.

Menggabungkan tiga tabel — rangkai JOIN secara berurutan

Untuk menggabungkan tiga tabel atau lebih, kamu cukup merangkai klausa `JOIN ... ON ...` satu demi satu.

Tulis FROM sales s JOIN employee e ON s.emp_id = e.emp_id JOIN department d ON e.dept_id = d.dept_id — pertama hubungkan sales ke employee lewat emp_id, lalu hubungkan hasilnya ke department lewat dept_id.

Cara paling alami membaca urutannya adalah: "mulai dari item penjualan (sales), lalu tempelkan info karyawan dan departemen di atasnya."

Semua INNER JOIN, jadi hanya baris yang cocok di ketiga tabel yang bertahan.

Di dataset ini, 12 karyawan yang punya penjualan semuanya tergabung dalam suatu departemen, sehingga INNER JOIN 3 tabel mengembalikan seluruh 50 baris sales.

-- Tempel nama sales dan dept_name ke tiap penjualan (INNER JOIN 3 tabel)
SELECT s.sale_id, e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY s.sale_id;

Bayangkan kebutuhan ini: "Saya mau laporan item penjualan dengan nama karyawan yang bertanggung jawab beserta nama departemennya." (Penjelasan akan muncul begitu kuerimu berjalan dengan benar.)

① INNER JOIN sales (alias s) dengan employee (alias e) lewat emp_id, lalu INNER JOIN hasilnya dengan department (alias d) lewat dept_id.

② Pilih empat kolom s.sale_id, e.name, d.dept_name, dan s.amount.

③ Urutkan hasilnya berdasarkan s.sale_id menaik.

Memfilter dengan WHERE, mengurutkan dengan ORDER BY

Setelah JOIN siap, pakai WHERE untuk menyimpan hanya baris yang kamu butuhkan dan ORDER BY untuk menatanya supaya enak dibaca.

Kondisi WHERE bisa merujuk kolom dari tabel mana pun yang sudah digabungkan.

WHERE d.location = 'Tokyo' menyaring agar hanya penjualan dari departemen di Tokyo yang tersisa, dan WHERE s.amount >= 400000 menyaring hanya penjualan bernilai tinggi.

Klausa SQL ditulis dalam urutan FROM → JOIN → ON → WHERE → ORDER BY.

WHERE memfilter baris setelah join selesai, dan ORDER BY mengurutkan paling akhir.

Untuk mengurutkan berdasarkan beberapa kolom, pisahkan dengan koma: ORDER BY col1 DESC, col2. Baris yang seri di col1 lalu diurutkan oleh col2 (kunci sekunder untuk menstabilkan urutan).

-- Hanya penjualan dari departemen yang berlokasi di Osaka, jumlah terbesar dulu
SELECT e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.location = 'Osaka'
ORDER BY s.amount DESC, s.sale_id;

Bayangkan kebutuhan ini: "Saya mau laporan penjualan di departemen yang berlokasi di Tokyo dengan nilai 400,000 atau lebih, urut jumlah menurun."

① INNER JOIN sales (s), employee (e), dan department (d) lewat emp_id dan dept_id.

② Filter ke baris dengan d.location adalah Tokyo dan s.amount minimal 400000.

③ Pilih tiga kolom e.name, d.dept_name, dan s.amount, urut s.amount menurun. Untuk baris yang seri di amount, pakai s.sale_id menaik sebagai kunci sekunder agar urutannya stabil.

Tambahkan label band dengan CASE — selesaikan dengan laporan terklasifikasi

Di atas JOIN, WHERE, dan ORDER BY, ekspresi CASE bisa menambahkan kolom berisi label band untuk jumlah, mengubah hasilnya menjadi laporan yang bisa dipindai manusia sekilas pandang.

CASE mengevaluasi kondisi dari atas ke bawah dan mengembalikan nilai cabang pertama yang bernilai benar. Kamu bisa menaruhnya langsung di dalam daftar kolom SELECT (bentuk searched: CASE WHEN kondisi THEN nilai ... ELSE default END).

Misalnya, bagi setiap penjualan ke tiga band berdasarkan amount: High jika amount >= 400000, Mid jika amount >= 200000, dan Low untuk sisanya — bandnya ditampilkan di kolom dengan alias band.

Pertahankan label sebagai string ASCII tetap ('High' / 'Mid' / 'Low') agar hasilnya portabel antar lingkungan.

Dengan JOIN 3 tabel, WHERE, ORDER BY, dan CASE digabung dalam satu kueri, kamu akan mendapatkan bentuk yang nyaris persis sama dengan laporan agregasi yang dibuat setiap hari di tim nyata.

-- Contoh: bagi amount jadi 2 band (Large >= 300000)
SELECT e.name, d.dept_name, s.amount,
  CASE
    WHEN s.amount >= 300000 THEN 'Large'
    ELSE 'Small'
  END AS size
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales'
ORDER BY s.sale_id;

Bayangkan kebutuhan ini: "Saya mau laporan penjualan dengan nama sales, nama departemen, dan label band, urut jumlah menurun."

① INNER JOIN sales (s), employee (e), dan department (d) di ketiga tabel.

② Tambahkan kolom dengan alias band memakai CASE bentuk searched yang mengembalikan 'High' jika s.amount minimal 400000, 'Mid' jika minimal 200000, dan 'Low' untuk sisanya.

③ Pilih empat kolom e.name, d.dept_name, s.amount, dan band, urut s.amount menurun dengan s.sale_id menaik sebagai pemecah seri.

Bayangkan kebutuhan ini: "Saya mau ringkasan yang menjumlahkan penjualan tiap sales dan memberi tingkatan Gold / Silver / Bronze berdasarkan total."

① INNER JOIN sales (s), employee (e), dan department (d).

② Kelompokkan berdasarkan e.name dan d.dept_name, dan agregasikan SUM(s.amount) dengan alias total.

③ Tambahkan kolom dengan alias tier memakai CASE bentuk searched yang mengembalikan 'Gold' jika SUM(s.amount) minimal 1500000, 'Silver' jika minimal 1000000, dan 'Bronze' untuk sisanya.

④ Pilih empat kolom e.name, d.dept_name, total, dan tier, urut total menurun dengan e.name menaik sebagai pemecah seri.

Karyawan tanpa penjualan hilang di INNER JOIN

Setiap laporan di artikel ini pakai INNER JOIN, jadi 18 karyawan tanpa penjualan tidak pernah muncul di hasil.

Kalau kamu butuh laporan yang menyertakan tiap karyawan — dengan nol untuk yang tidak punya penjualan — mulai dari employee sebagai tulang punggung dan LEFT JOIN sales ke atasnya, lalu bungkus SUM dengan COALESCE(SUM(s.amount), 0) agar total NULL berubah jadi 0.

Kapan pun angka pada laporan tidak cocok, hal pertama yang patut dicurigai adalah "jenis join yang salah sedang diam-diam menjatuhkan baris yang seharusnya ada."