Subkueri ② — Tabel Turunan, Subkueri SELECT, CREATE / INSERT SELECT

Data yang dipakai di artikel ini — employee / department / sales

Sebelumnya kamu memakai subkueri di WHERE.

Di artikel ini kamu akan membahas empat penerapan secara berurutan: menaruh subkueri di klausa `FROM` (tabel turunan), menulisnya di kolom `SELECT`, memadukannya dengan `CASE`, dan membangun tabel baru dari hasil subkueri.

Materinya adalah tiga tabel data karyawan.

Dengan employee (30 karyawan), department (6 departemen), dan sales (50 baris penjualan), kamu akan membangun tabel agregat per departemen dan menyandingkan total penjualan tiap karyawan sebagai sebuah kolom.

Sebelum memulai latihan, lihatlah definisi kolom dan contoh data dari tiga tabel yang dipakai artikel ini — employee / department / sales.

① Jalankan PRAGMA table_info(...) untuk memeriksa definisi kolom ketiga tabel.

② Jalankan SELECT * FROM nama_tabel LIMIT 5; untuk melihat 5 baris pertama tiap tabel.

Tabel turunan di FROM — perlakukan hasil subkueri sebagai satu tabel

Tabel turunan adalah cara menulis subkueri di klausa FROM sehingga hasilnya diperlakukan sebagai satu tabel virtual.

Kamu bisa lebih dulu membangun "rata-rata gaji per departemen" sebagai tabel agregat, lalu menerapkan JOIN atau WHERE lebih lanjut pada tabel itu.

Ini berguna saat kamu ingin mempersempit hasil yang sudah diagregasi satu langkah lagi.

Aturan kunci untuk tabel turunan adalah kamu wajib memberinya alias.

Tulis AS alias seperti FROM (SELECT ...) AS ds (SQLite juga memperbolehkan menghilangkan AS) — tanpa itu kamu akan dapat error.

Melalui alias itu kamu mengacu ke kolom tabel turunan, seperti ds.avg_salary.

-- Tabel turunan di FROM: agregasi jumlah orang dan rata-rata gaji per departemen, lalu join dengan department
SELECT d.dept_name, ds.headcount, ds.avg_salary
FROM (
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS ds
JOIN department d ON d.dept_id = ds.dept_id
ORDER BY ds.avg_salary DESC;

Bayangkan kebutuhan ini: "agregasi rata-rata gaji per departemen, lalu tampilkan hanya departemen yang rata-ratanya lebih tinggi dari 5.900.000, beserta nama departemennya." (Jalankan dengan benar dan penjelasannya akan muncul.)

① Di sebuah subkueri, agregasi employee berdasarkan dept_id dan bangun tabel turunan yang punya dept_id serta rata-rata gaji (alias avg_salary). Kecualikan karyawan yang dept_id-nya NULL dari agregasi.

② Beri tabel turunan sebuah alias, join dengan tabel department berdasarkan dept_id, dan ambil nama departemennya.

③ Di WHERE luar, persempit ke departemen yang avg_salary-nya lebih besar dari 5900000, lalu urutkan berdasarkan avg_salary menurun.

Subkueri di klausa SELECT — sematkan satu nilai sebagai kolom

Ketika kamu menulis subkueri skalar di daftar kolom SELECT, kamu bisa menambahkan kolom yang nilainya dihitung satu per baris.

Taruh (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id) di sebuah kolom, dan kamu bisa menyandingkan "total penjualan karyawan itu" sebagai satu kolom per karyawan.

Ini adalah subkueri terkorelasi yang mengacu ke emp.emp_id di luar.

-- Subkueri di klausa SELECT: tambahkan total penjualan tiap karyawan sebagai satu kolom
SELECT emp.name,
  COALESCE(
    (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id),
    0
  ) AS total_amount
FROM employee emp
ORDER BY emp.emp_id
LIMIT 6;

Karyawan tanpa penjualan membuat subkueri mengembalikan NULL, jadi menggantinya dengan 0 memakai COALESCE(..., 0) membuat tabel lebih mudah dibaca.

Subkueri di klausa SELECT juga harus muat dalam satu baris dan satu kolom.

Bayangkan kebutuhan ini: "di samping daftar semua karyawan, tampilkan total penjualan karyawan itu sebagai satu kolom tambahan, menampilkan 0 untuk karyawan tanpa penjualan."

① Beri tabel employee alias emp dan ambil name.

② Di kolom SELECT, tambahkan subkueri yang menghitung total sales karyawan itu, dialias total_amount. Korelasikan dengan sale.emp_id = emp.emp_id, dan ganti total dengan 0 untuk karyawan yang totalnya keluar NULL.

③ Urutkan berdasarkan total_amount menurun, jika seri pakai emp_id menaik, dan batasi ke 8 baris pertama.

Memadukan dengan CASE — tempelkan label percabangan berdasarkan nilai subkueri

Subkueri di klausa SELECT bekerja sama baiknya sebagai kondisi di CASE.

Kamu bisa membangun kolom peringkat seperti "jika total penjualan 1.500.000 atau lebih maka High, jika lebih dari 0 maka Mid, selain itu None" dengan menilai nilai subkueri pakai CASE WHEN.

Perhatikan bahwa kamu harus mengulang subkueri yang sama di tiap WHEN CASE (jika keterbacaan yang diutamakan, ada juga cara menulisnya hanya sekali dengan WITH (Common Table Expressions), yang akan kamu pelajari di bab berikutnya).

Di artikel ini kamu akan membahas bentuk dasar memadukan subkueri dan CASE secara langsung.

-- Klasifikasikan karyawan berdasarkan jumlah penjualan mereka
SELECT emp.name,
  (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) AS sale_count,
  CASE
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 4 THEN 'Frequent'
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 1 THEN 'Occasional'
    ELSE 'None'
  END AS activity
FROM employee emp
ORDER BY sale_count DESC, emp.emp_id
LIMIT 8;

Bayangkan kebutuhan ini: "untuk setiap karyawan, tampilkan total penjualan dan peringkat (High / Mid / None) berdampingan."

① Beri employee alias emp dan ambil name serta total penjualan karyawan itu (NULL diganti dengan 0, alias total_amount).

② Dengan CASE, tambahkan kolom peringkat dialias grade di mana total penjualan 1500000 atau lebih adalah 'High', lebih dari 0 dan kurang dari 1500000 adalah 'Mid', dan selain itu (0) adalah 'None'. Pakai subkueri total penjualan yang sama untuk penilaiannya juga.

③ Urutkan berdasarkan total_amount menurun, jika seri pakai emp_id menaik, dan batasi ke 8 baris pertama.

CREATE / INSERT SELECT — jadikan hasil subkueri sebuah tabel

Menulis CREATE TABLE tabel_baru AS SELECT ... memungkinkanmu menyimpan hasil SELECT langsung sebagai tabel baru (CTAS: Create Table As Select).

Ini sering dipakai saat kamu ingin menyimpan hasil agregat sebagai snapshot.

Ketika kamu ingin menambahkan baris ke tabel yang sudah ada, pakai INSERT INTO tabel_lama SELECT ... untuk menambahkan hasil SELECT secara massal.

Ini adalah operasi tulis yang membuat tabel atau menambahkan baris.

Di artikel ini kamu akan memakai tabel kerja sekali pakai top_seller untuk agregasi.

Supaya kamu mendapat hasil yang sama berapa kali pun dijalankan, kamu akan membersihkan tabel kerja dengan DROP TABLE IF EXISTS sebelum membuatnya ulang.

INSERT massal lebih cepat

INSERT INTO tabel SELECT ... menyisipkan baris target sekaligus dalam satu pernyataan.

Dibandingkan mengulang INSERT INTO tabel VALUES (...) untuk tiap baris, parsing SQL, pembaruan indeks, dan penanganan transaksi terjadi hanya sekali, jadi semakin banyak baris yang kamu sisipkan, semakin cepat jadinya.

Saat menyalin atau memindahkan tabel lain atau hasil agregat, lebih baik pakai INSERT ... SELECT massal ketimbang mengulang INSERT satu baris demi satu baris.

-- Simpan agregat per departemen ke tabel snapshot
DROP TABLE IF EXISTS dept_summary;
CREATE TABLE dept_summary AS
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id;

SELECT * FROM dept_summary ORDER BY avg_salary DESC;

Bayangkan kebutuhan ini: "kumpulkan karyawan yang total penjualannya 1.500.000 atau lebih ke dalam tabel agregat dengan 3 kolom — ID karyawan, nama, dan total penjualan." Karena ini operasi tulis, susun supaya tabel kerja dibuat ulang dan tidak rusak saat dijalankan lagi.

① Pertama, bersihkan tabel kerja jika ada dengan DROP TABLE IF EXISTS top_seller;.

② Join employee dan sales, agregasi total penjualan per karyawan, tulis SELECT yang hanya menyisakan karyawan yang totalnya 1500000 atau lebih, dan dari hasil itu bangun tabel top_seller dengan CREATE TABLE top_seller AS SELECT .... Buat kolomnya 3: emp_id, name, dan total_amount (total penjualan).

③ Terakhir, periksa isinya dengan SELECT * FROM top_seller ORDER BY total_amount DESC;.

Bayangkan kebutuhan ini: "ke tabel agregat dari Latihan 4, tambahkan juga karyawan tingkat menengah yang total penjualannya 900.000 atau lebih tetapi kurang dari 1.500.000." Pakai INSERT INTO ... SELECT untuk menambahkan baris secara massal. Supaya tidak rusak saat dijalankan ulang, susun untuk membuat ulang tabel sebelum menambahkan.

① Bersihkan tabel kerja dengan DROP TABLE IF EXISTS top_seller;, lalu buat ulang dengan CREATE TABLE top_seller AS SELECT ... yang sama (1,5 juta atau lebih) seperti Latihan 4.

② Dengan INSERT INTO top_seller SELECT ..., tambahkan karyawan yang total penjualannya 900000 atau lebih dan kurang dari 1500000 (3 kolom: emp_id, name, total penjualan). Buat urutan kolom cocok dengan top_seller.

③ Periksa semua baris dengan SELECT * FROM top_seller ORDER BY total_amount DESC;.