为什么我的 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中文网其他相关文章！