Pourquoi mon LEFT JOIN ne renvoie-t-il pas zéro compte dans PostgreSQL ?

PostgreSQL LEFT JOIN : garantir que les comptes zéro sont renvoyés

Un problème courant avec LEFT JOIN de PostgreSQL est l'incapacité de renvoyer des lignes avec un nombre nul. Cela se produit lorsque les conditions de filtrage sont mal placées.

Considérez cette requête, conçue pour compter les éléments d'examen par organisation, mais uniquement pour les organisations dont le nombre est supérieur à zéro :

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

Corriger la requête

Le problème réside dans la clause WHERE. Les conditions placées ici filtrent après la jointure, supprimant ainsi les organisations à décompte nul. La solution est de déplacer les conditions de filtrage dans la clause JOIN elle-même :

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

En intégrant les filtres dans le JOIN, nous garantissons que seules les lignes correspondantes sont incluses dans l'opération de jointure. Cela évite l'exclusion accidentelle d'organisations avec un décompte nul.

Remarques importantes :

Comportement de
• COUNT() : Contrairement à de nombreuses fonctions d'agrégation, COUNT() ne renvoie jamais NULL. Par conséquent, COALESCE(COUNT(col)) est redondant.
• Colonnes nulles : Appliquez COUNT() à une colonne NOT NULL ou assurez-vous que la condition de jointure garantit des valeurs non nulles.
• Optimisation : Pour des performances, en particulier avec des ensembles de données volumineux, envisagez de pré-agréger les décomptes dans une sous-requête avant de vous joindre à la table des organisations. Ceci est particulièrement avantageux lors du comptage de la plupart ou de la totalité des lignes.

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!