Mengapa PostgreSQL Saya Tidak Meninggalkan SERTAI Mengembalikan Baris dengan Kiraan 0?

PostgreSQL LEFT JOIN dan Hilang Kiraan Sifar

PostgreSQL LEFT JOIN direka untuk mengembalikan semua baris dari jadual kiri, walaupun tiada padanan dalam jadual kanan. Walau bagaimanapun, syarat klausa WHERE yang tidak diletakkan dengan betul boleh menukarkan LEFT JOIN menjadi INNER JOIN, meninggalkan baris dengan kiraan sifar.

Isu ini sering timbul apabila syarat penapisan berdasarkan jadual yang betul diletakkan dalam klausa WHERE. Pertimbangkan contoh ini:

<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>

Klausa WHERE menapis exam_items untuk item_template_id dan used = true khusus. Ini menapis selepas cantuman, mengalih keluar baris daripada organisations yang tidak mempunyai baris yang sepadan dalam exam_items memenuhi syarat ini.

Penyelesaian: Pindahkan Penapisan kepada Keadaan JOIN

Untuk mengekalkan semua baris daripada organisations, walaupun baris yang tidak sepadan dalam exam_items, alihkan syarat penapisan daripada klausa WHERE ke klausa JOIN:

<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 = true
GROUP  BY o.name
ORDER  BY o.name</code>

Sekarang, penapisan berlaku semasa penyertaan. Hanya exam_items baris yang memenuhi kriteria dipertimbangkan untuk penyertaan. organisations baris tanpa baris yang sepadan masih akan disertakan, menghasilkan nilai total_used 0.

Pengoptimuman Selanjutnya: COUNT() dan COALESCE

Pertanyaan asal menggunakan COALESCE(COUNT(exam_items.id)). Ini berlebihan. COUNT() tidak pernah kembali NULL; ia mengembalikan 0 jika tiada baris yang sepadan. Oleh itu, COALESCE tidak diperlukan dan boleh dialih keluar untuk kecekapan pertanyaan yang dipertingkatkan.

Atas ialah kandungan terperinci Mengapa PostgreSQL Saya Tidak Meninggalkan SERTAI Mengembalikan Baris dengan Kiraan 0?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!