Why Doesn't My PostgreSQL LEFT JOIN Return Rows with a Count of 0?

PostgreSQL LEFT JOIN and Missing Zero Counts

PostgreSQL's LEFT JOIN is designed to return all rows from the left table, even if there's no match in the right table. However, improperly placed WHERE clause conditions can effectively turn a LEFT JOIN into an INNER JOIN, omitting rows with zero counts.

This issue often arises when filtering conditions based on the right table are placed in the WHERE clause. Consider this example:

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

The WHERE clause filters exam_items for specific item_template_id and used = true. This filters after the join, removing rows from organisations that don't have matching rows in exam_items satisfying these conditions.

The Solution: Relocate Filtering to the JOIN Condition

To retain all rows from organisations, even those without matching rows in exam_items, move the filtering conditions from the WHERE clause to the JOIN clause:

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

Now, the filtering happens during the join. Only exam_items rows meeting the criteria are considered for the join. organisations rows without matching rows will still be included, resulting in total_used values of 0.

Further Optimization: COUNT() and COALESCE

The original query uses COALESCE(COUNT(exam_items.id)). This is redundant. COUNT() never returns NULL; it returns 0 if no rows match. Therefore, COALESCE is unnecessary and can be removed for improved query efficiency.

The above is the detailed content of Why Doesn't My PostgreSQL LEFT JOIN Return Rows with a Count of 0?. For more information, please follow other related articles on the PHP Chinese website!