Pourquoi mon PostgreSQL LEFT JOIN ne renvoie-t-il pas les lignes avec un nombre de 0 ?

PostgreSQL LEFT JOIN et nombres zéro manquants

Le LEFT JOIN de PostgreSQL est conçu pour renvoyer toutes les lignes de la table de gauche, même s'il n'y a aucune correspondance dans la table de droite. Cependant, des conditions de clause WHERE mal placées peuvent effectivement transformer un LEFT JOIN en un INNER JOIN, en omettant les lignes avec un nombre nul.

Ce problème survient souvent lorsque les conditions de filtrage basées sur la bonne table sont placées dans la clause WHERE. Prenons cet exemple :

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

La clause WHERE filtre exam_items pour des item_template_id et used = true spécifiques. Ceci filtre après la jointure, en supprimant les lignes de organisations qui n'ont pas de lignes correspondantes dans exam_items satisfaisant ces conditions.

La solution : déplacer le filtrage vers la condition JOIN

Pour conserver toutes les lignes de organisations, même celles sans lignes correspondantes dans exam_items, déplacez les conditions de filtrage de la clause WHERE vers la clause 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>

Maintenant, le filtrage se produit pendant la jointure. Seules les lignes exam_items répondant aux critères sont prises en compte pour la jointure. organisations les lignes sans lignes correspondantes seront toujours incluses, ce qui donnera des total_used valeurs de 0.

Optimisation supplémentaire : COUNT() et COALESCE

La requête d'origine utilise COALESCE(COUNT(exam_items.id)). C'est redondant. COUNT() ne revient jamais NULL ; il renvoie 0 si aucune ligne ne correspond. Par conséquent, COALESCE n'est pas nécessaire et peut être supprimé pour améliorer l'efficacité des requêtes.

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!