Mengapa Saya LEFT JOIN Mengembalikan Kiraan yang Salah dalam PostgreSQL?

PostgreSQL LEFT JOIN: Menangani Keputusan Kiraan Yang Salah

Apabila menggunakan LEFT JOIN dalam PostgreSQL untuk mengira entri bagi setiap organisasi, anda mungkin menghadapi situasi di mana hanya organisasi dengan kiraan lebih besar daripada sifar dikembalikan. Ini bercanggah dengan tingkah laku yang dijangkakan LEFT JOIN, yang harus merangkumi semua organisasi, tidak kira sama ada mereka mempunyai entri yang sepadan dalam jadual yang disertai.

Membetulkan Pertanyaan JOIN KIRI

Masalah biasanya timbul daripada meletakkan keadaan penapisan yang salah. Pertanyaan yang disemak berikut menunjukkan pendekatan yang betul:

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

Pengubahsuaian penting melibatkan pemindahan syarat e.used daripada klausa WHERE (di mana ia bertindak sebagai penapis selepas cantuman) ke klausa ON (di mana ia bertindak sebagai syarat cantum ). Ini memastikan bahawa hanya baris yang sepadan yang memenuhi semua syarat disertakan dalam gabungan itu sendiri, menghalang penapisan yang tidak diingini bagi organisasi dengan kiraan sifar.

Pendekatan Dioptimumkan: Pra-Penggabungan

Untuk prestasi yang lebih baik, terutamanya apabila berurusan dengan set data yang besar, pertimbangkan pertanyaan alternatif ini:

<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
    GROUP BY
      1
  ) AS e
  USING (id)
ORDER BY
  o.name,
  o.id;</code>

Kaedah ini mula-mula mengagregatkan kiraan dalam subkueri, kemudian melakukan gabungan. Prapengumpulan ini boleh meningkatkan kecekapan dengan ketara, terutamanya apabila sebahagian besar baris exam_items disertakan dalam kiraan. Fungsi COALESCE mengendalikan kes di mana total_used ialah NULL (untuk organisasi tanpa penyertaan yang sepadan), menggantikannya dengan 0 untuk konsistensi.

Atas ialah kandungan terperinci Mengapa Saya LEFT JOIN Mengembalikan Kiraan yang Salah dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!