Subkueri ① — Skalar, IN, dan Agregat di WHERE

Data yang dipakai di artikel ini — tabel employee

Subkueri adalah pernyataan SELECT yang ditulis di dalam kueri lain.

Kamu bisa lebih dulu menghitung "rata-rata gaji semua karyawan" lalu memakai hasil itu untuk mempersempit ke "karyawan yang gajinya di atas rata-rata" — sebuah kueri bertahap yang ditulis dalam satu pernyataan.

Di artikel ini kita akan membahas tiga bentuk yang dipakai di klausa WHERE — subkueri skalar, subkueri IN, dan agregat di dalam WHERE (subkueri terkorelasi) — satu per satu.

Sebelum latihan, mari kita lihat definisi kolom dan contoh data di tabel employee.

① Jalankan PRAGMA table_info(employee); untuk memeriksa nama kolom, tipe, dan primary key.

② Jalankan SELECT * FROM employee LIMIT 5; untuk melihat 5 baris pertama. Perhatikan bahwa sebagian karyawan punya NULL di kolom dept_id — kita akan menanganinya di latihan berikutnya.

Subkueri skalar — pakai hasil satu baris satu kolom sebagai nilai

Subkueri skalar (subkueri yang hasilnya tepat satu baris dan satu kolom) mengembalikan satu nilai tunggal, jadi kamu bisa menulisnya langsung di sisi kanan operator pembanding seperti = atau >.

(SELECT AVG(salary) FROM employee) mengembalikan satu nilai — "rata-rata gaji semua karyawan" — jadi WHERE salary > (SELECT AVG(salary) FROM employee) mempersempit ke "karyawan yang gajinya di atas rata-rata."

Poin pentingnya adalah membungkusnya dengan tanda kurung dan memastikan hasilnya selalu muat dalam satu baris satu kolom (mengembalikan banyak baris akan menjadi error).

Fungsi agregat seperti AVG / MAX / MIN / COUNT mengembalikan satu nilai, jadi sering dipakai di subkueri skalar.

-- Subkueri skalar: ambil karyawan yang gajinya sama dengan gaji tertinggi
SELECT name, salary
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

Bayangkan kebutuhan "tampilkan karyawan yang gajinya lebih tinggi dari rata-rata gaji semua karyawan." (Jalankan dengan benar dan penjelasannya akan muncul.)

① Ambil 2 kolom name dan salary dari tabel employee.

② Di kondisi WHERE, persempit ke baris yang salary-nya lebih besar dari rata-rata gaji semua karyawan. Hitung rata-rata gaji dengan subkueri skalar.

③ Urutkan berdasarkan salary secara menurun.

Subkueri IN — saring berdasarkan nilai yang ada di tabel lain

Ketika subkueri mengembalikan banyak baris dalam satu kolom, pakai IN alih-alih =.

Menulis WHERE kolom IN (SELECT kolom_lain FROM tabel_lain ...) hanya menyisakan baris yang nilainya ada di himpunan yang dikembalikan subkueri.

Subkueri di kanan IN me-SELECT hanya satu kolom (mengembalikan banyak kolom akan menjadi error).

Untuk menegasikannya kamu pakai NOT IN, tapi ada jebakan: jika NULL tercampur di sisi subkueri, hasilnya berubah (kita bahas ini di akhir artikel).

-- Subkueri IN: karyawan di departemen yang berlokasi di Tokyo
SELECT name, dept_id
FROM employee
WHERE dept_id IN (
  SELECT dept_id FROM department WHERE location = 'Tokyo'
);

Bayangkan kebutuhan "tampilkan karyawan yang muncul setidaknya sekali di catatan penjualan (karyawan yang benar-benar membukukan penjualan)."

① Ambil 2 kolom emp_id dan name dari tabel employee.

② Bangun himpunan ID karyawan yang muncul di tabel sales dengan subkueri, lalu pakai IN untuk mempersempit ke baris yang emp_id-nya ada di himpunan itu.

③ Urutkan berdasarkan emp_id secara menaik.

Agregat di dalam WHERE — banding dengan rata-rata departemen pakai subkueri terkorelasi

Sebelum subkueri terkorelasi, mari luruskan dulu struktur dua tingkat dari subkueri.

Ada SELECT utama yang menarik daftar karyawan, dan di dalamnya, di dalam tanda kurung, kamu menulis SELECT lain (subkueri).

Subkueri sejauh ini adalah subkueri tak terkorelasi — dihitung hanya sekali di awal, terlepas dari SELECT utama (misalnya, rata-rata gaji seluruh perusahaan adalah satu nilai yang sama tak peduli karyawan mana yang kamu lihat).

Sebaliknya, subkueri yang memakai kolom dari karyawan yang sedang diproses SELECT utama disebut subkueri terkorelasi (subkueri yang dihitung ulang untuk setiap karyawan).

Misalnya, untuk menampilkan "karyawan dengan gaji tertinggi di departemen (karyawan yang gajinya sama dengan gaji maksimum departemennya sendiri)," gaji maksimum berbeda per departemen, jadi untuk setiap karyawan kamu butuh gaji maksimum dari departemen itu saja.

Di contoh di bawah, karyawan utama diberi alias emp dan sisi subkueri dept_member, dan WHERE dept_member.dept_id = emp.dept_id mengorelasikan keduanya.

Ketika karyawan berubah, dept_id yang diteruskan juga berubah, dan hasilnya dihitung ulang.

-- Subkueri terkorelasi: karyawan yang gajinya sama dengan gaji maksimum departemennya sendiri (yang tertinggi di departemen)
SELECT emp.name, emp.dept_id, emp.salary
FROM employee emp
WHERE emp.salary = (
    SELECT MAX(dept_member.salary)
    FROM employee dept_member
    WHERE dept_member.dept_id = emp.dept_id
  );

Bayangkan kebutuhan "di dalam tiap departemen, tampilkan karyawan yang gajinya lebih tinggi dari rata-rata gaji departemen itu."

① Beri alias emp pada tabel employee dan ambil 3 kolom name, dept_id, dan salary.

② Batasi ke baris yang dept_id-nya bukan NULL (karyawan tanpa departemen di luar lingkup).

③ Persempit ke baris yang salary-nya lebih besar dari rata-rata gaji departemen yang sama dengan karyawan itu. Hitung rata-rata departemen dengan subkueri beralias dept_member, dikorelasikan dengan dept_member.dept_id = emp.dept_id.

④ Urutkan berdasarkan dept_id menaik, dan dalam departemen yang sama berdasarkan salary menurun.

Jebakan NOT IN dan NULL

Negasi dari IN adalah NOT IN, tapi jika NULL tercakup di subkueri di kanan NOT IN, hasilnya berubah drastis.

Itu karena ketika menjadi NOT IN (..., NULL), SQL memeriksa "nilai tidak sama dengan satu pun dari mereka" dalam bentuk nilai <> ... AND nilai <> NULL, tapi nilai <> NULL tidak menghasilkan true maupun false (hasilnya NULL), jadi keseluruhan AND tidak pernah menjadi true dan tidak ada baris yang bertahan.

Bayangkan kebutuhan "tampilkan nama karyawan yang bukan atasan (bos) siapa pun." Kolom manager_id di employee berisi NULL untuk karyawan yang tidak punya bos. (Jalankan dengan benar dan penjelasannya akan muncul.)

① Jalankan NOT IN di editor apa adanya dan pastikan hasilnya keluar sebagai 0 baris.

② Kecualikan baris yang manager_id-nya NULL di sisi subkueri, dan perbaiki supaya karyawan yang bukan atasan siapa pun keluar dengan benar.

③ Urutkan berdasarkan emp_id secara menaik.