Warum gibt mein LEFT JOIN in PostgreSQL falsche Zählungen zurück?

PostgreSQL LEFT JOIN: Behebung falscher Zählergebnisse

Wenn Sie in PostgreSQL einen LEFT JOIN verwenden, um Einträge pro Organisation zu zählen, kann es vorkommen, dass nur Organisationen zurückgegeben werden, deren Anzahl größer als Null ist. Dies widerspricht dem erwarteten Verhalten eines LEFT JOIN, der alle Organisationen umfassen sollte, unabhängig davon, ob sie übereinstimmende Einträge in der verbundenen Tabelle haben.

Korrektur der LEFT JOIN-Abfrage

Das Problem entsteht typischerweise durch die falsche Platzierung von Filterbedingungen. Die folgende überarbeitete Abfrage zeigt den richtigen Ansatz:

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

Die entscheidende Änderung besteht darin, die e.used-Bedingung aus der WHERE-Klausel (wo sie als Filter nach dem Join fungiert) in die ON-Klausel (wo sie als Join-Bedingung fungiert) zu verschieben ). Dadurch wird sichergestellt, dass nur übereinstimmende Zeilen, die alle Bedingungen erfüllen, in den Join selbst einbezogen werden, wodurch die unbeabsichtigte Filterung von Organisationen mit Nullzählungen verhindert wird.

Optimierter Ansatz: Voraggregation

Für eine verbesserte Leistung, insbesondere beim Umgang mit großen Datensätzen, ziehen Sie diese alternative Abfrage in Betracht:

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

Diese Methode aggregiert zunächst die Zählungen innerhalb einer Unterabfrage und führt dann den Join durch. Diese Vorabaggregation kann die Effizienz erheblich steigern, insbesondere wenn ein erheblicher Teil der exam_items-Zeilen in die Zählung einbezogen wird. Die Funktion COALESCE behandelt Fälle, in denen total_used NULL ist (für Organisationen ohne übereinstimmende Einträge) und ersetzt sie aus Konsistenzgründen durch 0.

Das obige ist der detaillierte Inhalt vonWarum gibt mein LEFT JOIN in PostgreSQL falsche Zählungen zurück?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!