EXISTS dan Subkueri Terkorelasi

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

EXISTS adalah subkueri yang memeriksa hanya apakah setidaknya satu baris yang cocok ada, dan kamu memakainya mirip seperti IN.

Di artikel ini kamu akan membahas dasar EXISTS / NOT EXISTS, cara menghindari jebakan NOT IN + NULL dengan aman dengan menulisnya pakai EXISTS, dan cara memilih antara EXISTS dan IN berdasarkan keterbacaan.

Materinya adalah tiga tabel data karyawan.

Dengan employee (30 karyawan), sales (50 baris penjualan), dan department (6 departemen), kamu akan mengambil "karyawan dengan / tanpa penjualan" dan "departemen tanpa karyawan".

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

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

② Jalankan SELECT * FROM nama_tabel LIMIT 5; untuk melihat 5 baris pertama tiap tabel. Fakta bahwa employee.dept_id bisa NULL juga muncul di latihan berikutnya.

EXISTS / NOT EXISTS — cukup periksa apakah ada baris

WHERE EXISTS (SELECT ... ) bernilai true jika subkueri mengembalikan setidaknya satu baris, dan false jika tidak mengembalikan baris sama sekali.

Apa yang dipilih subkueri tidak memengaruhi hasil, jadi secara konvensi kamu menulis SELECT 1 (nilainya tidak dipakai — kamu hanya peduli apakah ada baris).

Bentuk dasarnya adalah subkueri terkorelasi yang mengacu ke kolom dari kueri utama di dalam EXISTS.

"Karyawan dengan penjualan" adalah WHERE EXISTS (SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id), dan "karyawan tanpa penjualan" adalah negasinya, NOT EXISTS.

EXISTS berhenti mengevaluasi begitu menemukan satu baris, yang membuatnya cocok untuk pemeriksaan keberadaan.

-- EXISTS: karyawan dengan penjualan
SELECT emp.emp_id, emp.name
FROM employee emp
WHERE EXISTS (
  SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id
)
ORDER BY emp.emp_id;

Bayangkan kebutuhan "saya ingin daftar karyawan yang tidak muncul di baris penjualan sekali pun (karyawan yang belum membukukan penjualan apa pun)." (Jalankan dengan benar dan penjelasannya akan muncul.)

① Beri tabel employee alias e dan ambil emp_id serta name.

② Pakai NOT EXISTS untuk mempersempit ke baris yang karyawannya tidak punya baris sama sekali di sales. Korelasikan subkueri dengan kueri utama dalam bentuk SELECT 1 FROM sales sale WHERE sale.emp_id = emp.emp_id.

③ Urutkan berdasarkan emp_id secara menaik.

Jebakan NOT IN + NULL — EXISTS aman

Jika kamu menulis "penyaringan negatif" dengan NOT IN, hasilnya bisa kembali 0 baris ketika NULL tercampur di sisi subkueri.

x NOT IN (1, 2, NULL) dievaluasi sebagai "x bukan 1, bukan 2, dan bukan NULL," tapi x <> NULL tidak pernah menjadi true dan selalu NULL (unknown), jadi keseluruhan NOT IN tidak pernah menjadi true.

Penalaran lengkapnya dijelaskan di Subkueri ① (jebakan NOT IN + NULL).

Karena employee.dept_id mencampurkan NULL, menulis "departemen tanpa karyawan" sebagai WHERE dept_id NOT IN (SELECT dept_id FROM employee) membuat Legal (departemen tanpa karyawan sama sekali), yang seharusnya ada, menghilang dan hasilnya kembali 0 baris.

NOT EXISTS hanya melihat apakah ada baris dan tidak terpengaruh NULL, jadi dalam situasi ini NOT EXISTS mengembalikan hasil yang benar.

-- Jebakan: subkueri yang mengandung NULL + NOT IN kembali 0 baris
SELECT dept_id, dept_name
FROM department
WHERE dept_id NOT IN (SELECT dept_id FROM employee);
-- → 0 baris karena employee.dept_id mengandung NULL

-- Aman: NOT EXISTS tidak terpengaruh NULL
SELECT d.dept_id, d.dept_name
FROM department d
WHERE NOT EXISTS (
  SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id
);

Bayangkan kebutuhan "saya ingin mendaftar departemen yang tidak punya karyawan ditugaskan padanya sama sekali." Karena employee.dept_id mencampurkan NULL, tulis dengan aman memakai NOT EXISTS.

① Beri tabel department alias d dan ambil dept_id serta dept_name.

② Pakai NOT EXISTS untuk mempersempit ke baris yang departemennya tidak punya baris karyawan sama sekali di employee. Korelasikan subkueri dalam bentuk SELECT 1 FROM employee emp WHERE emp.dept_id = d.dept_id.

③ Urutkan berdasarkan dept_id secara menaik.

Periksa apa yang terjadi ketika kamu menulis latihan sebelumnya dengan NOT IN. Latihan ini dimaksudkan supaya kamu mengalami kesalahannya langsung, jadi jalankan sebagai NOT IN dan amati bahwa hasilnya kembali 0 baris.

① Ambil dept_id dan dept_name dari tabel department.

② Coba daftar "departemen tanpa karyawan" memakai WHERE dept_id NOT IN (SELECT dept_id FROM employee).

③ Jalankan dan pastikan hasilnya 0 baris (departemen Legal tidak keluar). Pikirkan kenapa hasilnya berbeda dari versi NOT EXISTS di Latihan 2.

Pilih EXISTS berdasarkan keterbacaan

EXISTS hanya melihat apakah ada baris, jadi aman bahkan ketika NULL hadir, tapi menulis subkueri terkorelasi bisa membuatnya terlihat lebih panjang daripada IN.

Untuk pemeriksaan keberadaan sederhana ada situasi di mana IN lebih enak dibaca, dan di kerja nyata kamu memilih berdasarkan konteks.

Di sisi lain, untuk negasi seperti NOT IN di mana NULL merusak hasil, atau ketika kamu ingin menilai dengan andal hanya apakah ada baris, EXISTS / NOT EXISTS adalah pilihan aman.