Warum gibt meine LEFT JOIN-Abfrage keine Organisationen mit der Anzahl Null zurück?

PostgreSQL LEFT JOIN: Behebung von Zero-Count-Organisationsproblemen

Eine häufige Herausforderung bei der Verwendung von LEFT JOIN in PostgreSQL besteht darin, die Anzahl der zugehörigen Datensätze abzurufen. Wenn die Anzahl für eine bestimmte Organisation Null ist, wird diese Organisation möglicherweise aus den Ergebnissen weggelassen. Dieser Artikel befasst sich mit diesem Problem.

Die folgende Abfrage versucht, gebrauchte Artikel pro Organisation zu zählen:

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

Der Fehler dieser Abfrage liegt in der WHERE-Klausel. Bedingungen, die innerhalb der WHERE-Klausel platziert werden, filtern nach dem Join und wandeln das LEFT JOIN effektiv in ein INNER JOIN um, wenn in exam_items kein passender Datensatz gefunden wird.

Hier sind verbesserte Versionen:

Lösung 1: Korrigieren der JOIN-Bedingung

Die WHERE-Klausel filtert Organisationen mit Nullzählungen heraus. Das Verschieben der item_template_id- und used-Bedingungen in die ON-Klausel von LEFT JOIN löst Folgendes:

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

Diese überarbeitete Abfrage stellt sicher, dass alle Organisationen einbezogen werden, auch diejenigen ohne übereinstimmende Einträge in exam_items.

Lösung 2: Vorabaggregation für Effizienz

Bei größeren Datensätzen kann die Voraggregierung exam_items die Leistung erheblich verbessern:

<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 = TRUE
   GROUP BY 1
) e ON o.id = e.id
ORDER BY o.name, o.id;</code>

Dieser Ansatz aggregiert zunächst die Zählungen für jede Organisation und führt dann LEFT JOIN durch, was zu einer effizienteren Abfrage führt. Beachten Sie die Verwendung von COALESCE zur Behandlung von Fällen, in denen total_used NULL ist.

Durch die Implementierung einer dieser Lösungen können Sie die Anzahl der verwendeten Artikel für alle Organisationen genau abrufen, auch für diejenigen mit einer Anzahl von Null. Wählen Sie die Lösung, die am besten zu Ihrer Datengröße und Ihren Leistungsanforderungen passt.

Das obige ist der detaillierte Inhalt vonWarum gibt meine LEFT JOIN-Abfrage keine Organisationen mit der Anzahl Null zurück?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!