Pourquoi mon LEFT JOIN ne renvoie-t-il pas un nombre nul pour les organisations ?

Dépannage des requêtes LEFT JOIN : comptes nuls et optimisation

Un problème courant lors de l'utilisation de LEFT JOIN est l'échec du retour de zéro pour certains groupes. Cela se produit souvent lorsque les conditions de filtrage sont placées dans la clause WHERE au lieu de la clause JOIN. Cet exemple montre comment structurer correctement un LEFT JOIN pour garantir que toutes les organisations sont incluses, même celles avec un décompte nul.

Corriger le LEFT JOIN

Le problème vient d'un placement incorrect des clauses WHERE. Considérez cette requête corrigée :

<code class="language-sql">LEFT JOIN exam_items e ON e.organisation_id = o.id
                         AND e.item_template_id = #{sanitize(item_template_id)}
                         AND e.used</code>

Placer des conditions de filtrage (e.item_template_id, e.used) dans la clause JOIN garantit que seules les lignes correspondantes sont jointes. Auparavant, placer ces conditions dans la clause WHERE transformait par inadvertance LEFT JOIN en INNER JOIN, excluant les organisations sans entrées correspondantes.

COMPTE(e.id) Comportement

Il est crucial de comprendre que COUNT(*) et COUNT(e.id) renvoient toujours une valeur numérique (0 ou supérieure), contrairement à d'autres fonctions d'agrégation qui peuvent renvoyer NULL. Par conséquent, utiliser COALESCE pour gérer les valeurs NULL n'est pas nécessaire dans ce cas.

Optimisation des performances : regrouper avant de rejoindre

Pour les scénarios dans lesquels la majorité des exam_items lignes sont comptées, une amélioration des performances est obtenue en pré-agrégeant les décomptes :

<code class="language-sql">LEFT JOIN (
   SELECT organisation_id AS id  -- aliased for simpler join
        , COUNT(*) AS total_used  -- COUNT(*) is generally faster
   FROM   exam_items
   WHERE  item_template_id = #{sanitize(item_template_id)}
   AND    used
   GROUP  BY 1
   ) e USING (id)</code>

Cette approche optimisée regroupe d'abord les décomptes de exam_items, puis effectue une jointure plus simple avec la table des organisations. Cela peut améliorer considérablement la vitesse des requêtes, en particulier avec de grands ensembles de données.

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!