Mengapa Pertanyaan LEFT JOIN Saya Tidak Mengembalikan Organisasi dengan Kiraan Sifar?

PostgreSQL LEFT JOIN: Menangani Isu Organisasi Kiraan Sifar

Cabaran biasa apabila menggunakan LEFT JOIN dalam PostgreSQL melibatkan mendapatkan semula kiraan rekod yang berkaitan. Jika kiraan adalah sifar untuk organisasi tertentu, organisasi itu mungkin diabaikan daripada keputusan. Artikel ini menangani masalah ini.

Pertanyaan berikut cuba mengira item terpakai bagi setiap organisasi:

<code class="language-sql">SELECT o.name as organisation_name,
         coalesce(COUNT(exam_items.id)) as total_used
  FROM organisations o
  LEFT JOIN exam_items e on o.id = e.organisation_id
  WHERE e.item_template_id = #{sanitize(item_template_id)}
  and e.used = true
  GROUP BY o.name
  ORDER BY o.name</code>

Kesilapan pertanyaan ini terletak pada klausa WHERE. Syarat yang diletakkan dalam WHERE penapis klausa selepas gabungan, menjadikan LEFT JOIN menjadi INNER JOIN dengan berkesan apabila rekod yang sepadan tidak ditemui dalam exam_items.

Berikut ialah versi yang dipertingkatkan:

Penyelesaian 1: Membetulkan Syarat JOIN

Fasal WHERE menapis organisasi dengan kiraan sifar. Memindahkan syarat item_template_id dan used ke dalam klausa ON LEFT JOIN menyelesaikan perkara ini:

<code class="language-sql">SELECT o.name AS organisation_name, COUNT(e.id) AS total_used
FROM organisations o
LEFT JOIN exam_items e ON e.organisation_id = o.id AND e.item_template_id = #{sanitize(item_template_id)} AND e.used
GROUP BY o.name
ORDER BY o.name;</code>

Pertanyaan yang disemak ini memastikan semua organisasi disertakan, walaupun yang tiada entri yang sepadan dalam exam_items.

Penyelesaian 2: Pra-Penggabungan untuk Kecekapan

Untuk set data yang lebih besar, pra-pengagregatan exam_items boleh meningkatkan prestasi dengan ketara:

<code class="language-sql">SELECT o.id, o.name AS organisation_name, COALESCE(e.total_used, 0) AS total_used
FROM organisations o
LEFT JOIN (
   SELECT organisation_id AS id, COUNT(*) AS total_used
   FROM exam_items
   WHERE item_template_id = #{sanitize(item_template_id)} AND used = TRUE
   GROUP BY 1
) e ON o.id = e.id
ORDER BY o.name, o.id;</code>

Pendekatan ini mula-mula mengagregatkan kiraan untuk setiap organisasi dan kemudian melaksanakan LEFT JOIN, menghasilkan pertanyaan yang lebih cekap. Perhatikan penggunaan COALESCE untuk mengendalikan kes di mana total_used adalah NULL.

Dengan melaksanakan salah satu daripada penyelesaian ini, anda akan mendapatkan semula kiraan item terpakai dengan tepat untuk semua organisasi, termasuk yang mempunyai kiraan sifar. Pilih penyelesaian yang paling sesuai dengan saiz data dan keperluan prestasi anda.

Atas ialah kandungan terperinci Mengapa Pertanyaan LEFT JOIN Saya Tidak Mengembalikan Organisasi dengan Kiraan Sifar?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!