为什么我的 PostgreSQL LEFT JOIN 不返回计数为 0 的行？

PostgreSQL LEFT JOIN 和缺失零计数

PostgreSQL 的 LEFT JOIN 旨在返回左表中的所有行，即使右表中没有匹配项。 但是，放置不当的 WHERE 子句条件可以有效地将 LEFT JOIN 变成 INNER JOIN，从而忽略计数为零的行。

当基于右表的过滤条件放在WHERE子句中时，经常会出现此问题。 考虑这个例子：

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

WHERE 子句筛选特定 exam_items 和 item_template_id 的 used = true。这会在连接之后过滤，从organisations中删除exam_items中没有满足这些条件的匹配行的行。

解决方案：将过滤重新定位到 JOIN 条件

要保留 organisations 中的所有行，即使是 exam_items 中没有匹配行的行，请将过滤条件从 WHERE 子句移至 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 = true
GROUP  BY o.name
ORDER  BY o.name</code>

现在，过滤发生在连接期间。仅考虑满足条件的 exam_items 行进行连接。 仍将包含没有匹配行的 organisations 行，导致 total_used 值为 0。

进一步优化：COUNT() 和 COALESCE

原始查询使用COALESCE(COUNT(exam_items.id))。这是多余的。 COUNT() 永远不会返回 NULL；如果没有行匹配则返回 0。 因此，COALESCE是不必要的，可以将其删除以提高查询效率。

以上是为什么我的 PostgreSQL LEFT JOIN 不返回计数为 0 的行？的详细内容。更多信息请关注PHP中文网其他相关文章！