為什麼我的 LEFT JOIN 在 PostgreSQL 中傳回不正確的計數？

PostgreSQL LEFT JOIN：解不正確的計數結果

在 PostgreSQL 中使用 LEFT JOIN 對每個組織的條目進行計數時，您可能會遇到僅傳回計數大於零的組織的情況。 這與 LEFT JOIN 的預期行為相矛盾，LEFT JOIN 應包括所有組織，無論它們在連接表中是否有匹配的條目。

更正 LEFT 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
GROUP BY
  o.name
ORDER BY
  o.name;</code>

關鍵的修改涉及將 e.used 條件從 WHERE 子句（它充當 連接之後的過濾器）移動到 ON 子句（其中它充當連接條件） ）。這可確保只有滿足所有條件的符合行才會包含在連線本身中，從而防止意外過濾計數為零的組織。

最佳化方法：預先聚合

為了提高效能，特別是在處理大型資料集時，請考慮以下替代查詢：

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

此方法首先聚合子查詢中的計數，然後執行連線。 這種預聚合可以顯著提高效率，特別是當計數中包含大部分 exam_items 行時。 COALESCE 函數處理 total_used 為 NULL 的情況（對於沒有匹配條目的組織），將其替換為 0 以保持一致性。

以上是為什麼我的 LEFT JOIN 在 PostgreSQL 中傳回不正確的計數？的詳細內容。更多資訊請關注PHP中文網其他相關文章！