Klausa WITH — Menyusun Kueri Bertahap dengan Hasil Antara Bernama

Data yang dipakai pada artikel ini — tabel employee dan department

CTE (Common Table Expression) adalah mekanisme yang menulis WITH name AS (SELECT ...) untuk memberi nama pada sebuah subkueri, lalu mereferensikan nama itu dalam kueri-kueri berikutnya.

Karena kamu bisa memecah subkueri yang ber-nesting dalam menjadi beberapa tahap, kodenya jadi jauh lebih mudah dibaca.

Datanya adalah tabel employee (30 baris; kolom manager_id menunjuk ke emp_id manager, sebuah struktur self-referential) dan tabel department (6 baris).

Kamu akan mengerjakan latihan yang memakai CTE untuk menulis agregasi multi-tahap dengan lebih mudah dibaca.

Sebelum memulai latihan, lihat dulu definisi kolom dan data contoh dari dua tabel yang dipakai artikel ini — employee dan department.

① Jalankan PRAGMA table_info(employee); dan PRAGMA table_info(department); untuk memeriksa definisi kolom kedua tabel.

② Jalankan SELECT * FROM employee LIMIT 5; dan SELECT * FROM department LIMIT 5; untuk meninjau 5 baris pertama dari masing-masing. Perhatikan juga bagaimana NULL muncul pada kolom manager_id (karyawan yang tidak punya manager).

Membangun hasil antara bernama dengan WITH — bikin kueri multi-tahap mudah dibaca

Ketika kamu menulis WITH name AS (SELECT ...), hasil dari SELECT di dalamnya akan diberi nama, dan kamu bisa mereferensikannya dari FROM atau JOIN di kueri utama berikutnya.

Daripada menulis proses dua langkah "pertama buat hasil antara, lalu pakai itu untuk menghasilkan hasil akhir" sebagai subkueri ber-nesting, kamu bisa memecahnya menjadi bentuk yang dibaca dari atas ke bawah.

CTE adalah nama sementara yang hilang setelah kueri utama selesai — ia tidak membuat tabel sungguhan.

Sintaksnya adalah WITH name AS ( ... ) SELECT ... FROM name ...;.

-- Mengelompokkan karyawan bergaji tinggi ke dalam CTE high_earner,
-- lalu join dengan department untuk menambahkan nama departemen
WITH high_earner AS (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
)
SELECT h.name, h.salary, d.dept_name
FROM high_earner h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

Pada kode di atas, nama high_earner memotong hasil antara "karyawan dengan salary >= 7.000.000", dan kueri utama mereferensikannya seperti sebuah tabel dengan FROM high_earner.

Begini alurnya kalau dilihat sebagai diagram.

Kamu bisa menulis proses yang sama dengan subkueri (sebuah SELECT yang ber-nesting di dalam tanda kurung SELECT lain).

Mari kita bangun ulang versi WITH sebelumnya sebagai subkueri alih-alih CTE.

-- Proses yang sama ditulis sebagai subkueri (derived table)
-- Sebuah SELECT berada di dalam tanda kurung FROM, jadi kamu membaca luar → dalam → luar
SELECT h.name, h.salary, d.dept_name
FROM (
  SELECT emp_id, name, dept_id, salary
  FROM employee
  WHERE salary >= 7000000
) AS h
LEFT JOIN department d ON h.dept_id = d.dept_id
ORDER BY h.salary DESC;

Hasilnya persis sama dengan versi WITH, tapi pada versi subkueri kamu harus menyelam ke dalam SELECT yang ber-nesting di dalam FROM untuk mengetahui apa yang dilakukan SELECT di dalamnya.

Versi WITH memotong hasil antara dengan nama `high_earner`, jadi dua tahap "① menyaring karyawan bergaji tinggi → ② menambahkan nama departemen" terbaca lurus dari atas ke bawah.

Kesenjangan ini makin besar saat kamu menambah tahap antara — saat kamu sudah punya 3 atau 4 tahap, subkueri yang ber-nesting dalam jadi sulit diikuti.

Kebutuhan: "Saya ingin memotong karyawan yang tidak punya manager (karyawan level executive dengan manager_id = NULL) dan menampilkannya bersama nama departemen mereka." Selesaikan dengan dua tahap memakai CTE. (Kalau dijalankan dengan benar, penjelasannya akan muncul.)

① Dengan WITH, definisikan sebuah CTE bernama executive yang mengambil baris dari employee di mana manager_id adalah NULL (emp_id, name, dept_id).

② LEFT JOIN CTE itu dengan department pada dept_id, dan kembalikan name, dept_id, dept_name.

③ Urutkan hasilnya berdasarkan name menaik.

Rangkai beberapa klausa WITH — pecah agregasi multi-tahap satu langkah pada satu waktu

Setelah WITH, kamu bisa mendaftar beberapa CTE dipisahkan koma.

Bentuknya adalah WITH a AS (...), b AS (...) SELECT ..., dan CTE belakangan bisa mereferensikan yang sebelumnya.

Ini membuat kamu bisa memecah agregasi multi-tahap ("hitung rata-rata per departemen" → "pilih karyawan di atas rata-rata itu" → "tambahkan nama departemen") menjadi tahap-tahap bernama satu per satu, sehingga bahkan pada kueri yang panjang kamu bisa menjaga peran setiap tahap tetap terpisah.

-- ① dept_avg: rata-rata gaji per departemen
-- ② top_earner: karyawan di atas rata-rata departemennya sendiri
-- ③ Kueri utama: tambahkan nama departemen dan tampilkan
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
),
top_earner AS (
  SELECT e.name, e.dept_id, e.salary
  FROM employee e
  JOIN dept_avg da ON e.dept_id = da.dept_id
  WHERE e.salary > da.avg_salary
)
SELECT d.dept_name, t.name, t.salary
FROM top_earner t
JOIN department d ON t.dept_id = d.dept_id
ORDER BY d.dept_name, t.salary DESC;

Kebutuhan: "Saya ingin menghitung rata-rata gaji setiap departemen, lalu menampilkan hanya karyawan yang gajinya melebihi rata-rata itu, dengan nama departemen mereka." Selesaikan dengan merangkai dua CTE.

① Untuk CTE pertama, agregasikan employee berdasarkan dept_id dan hasilkan rata-rata gaji per departemen. Singkirkan karyawan yang dept_id-nya NULL dari rata-rata.

② Untuk CTE kedua, join employee dengan CTE pertama pada dept_id dan pilih karyawan (name, dept_id, salary) yang salary-nya lebih besar dari rata-rata departemen itu.

③ Pada kueri utama, join CTE kedua dengan department pada dept_id dan kembalikan dept_name, name, salary. Urutkan berdasarkan nama departemen menaik, dan di dalam departemen yang sama berdasarkan gaji menurun.