Pourquoi mon LEFT JOIN renvoie-t-il des comptes incorrects dans PostgreSQL ?

PostgreSQL LEFT JOIN : Correction des résultats de comptage incorrects

Lorsque vous utilisez un LEFT JOIN dans PostgreSQL pour compter les entrées par organisation, vous pouvez rencontrer une situation dans laquelle seules les organisations dont le nombre est supérieur à zéro sont renvoyées. Cela contredit le comportement attendu d'un LEFT JOIN, qui devrait inclure toutes les organisations, qu'elles aient ou non des entrées correspondantes dans la table jointe.

Correction de la requête LEFT JOIN

Le problème provient généralement d'un placement incorrect des conditions de filtrage. La requête révisée suivante démontre l'approche correcte :

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

La modification cruciale consiste à déplacer la condition e.used de la clause WHERE (où elle agit comme un filtre après la jointure) vers la clause ON (où elle agit comme une condition de jointure ). Cela garantit que seules les lignes correspondantes satisfaisant toutes les conditions sont incluses dans la jointure elle-même, empêchant ainsi le filtrage involontaire des organisations avec un nombre nul.

Approche optimisée : Pré-agrégation

Pour améliorer les performances, en particulier lorsque vous traitez des ensembles de données volumineux, envisagez cette requête alternative :

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

Cette méthode regroupe d'abord les décomptes dans une sous-requête, puis effectue la jointure. Cette pré-agrégation peut améliorer considérablement l'efficacité, en particulier lorsqu'une partie substantielle des exam_items lignes est incluse dans le décompte. La fonction COALESCE gère les cas où total_used est NULL (pour les organisations sans entrées correspondantes), en le remplaçant par 0 pour des raisons de cohérence.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!