Why Doesn't My LEFT JOIN Return Zero Counts in PostgreSQL?

PostgreSQL LEFT JOIN: Ensuring Zero Counts Are Returned

A common issue with PostgreSQL's LEFT JOIN is the failure to return rows with zero counts. This occurs when filtering conditions are incorrectly placed.

Consider this query, designed to count exam items per organization, but only returning organizations with counts above zero:

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

Correcting the Query

The problem lies in the WHERE clause. Conditions placed here filter after the join, effectively removing zero-count organizations. The solution is to move the filtering conditions into the JOIN clause itself:

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

By integrating the filters within the JOIN, we ensure that only matching rows are included in the join operation. This prevents the accidental exclusion of organizations with zero counts.

Important Notes:

• COUNT()'s Behavior: Unlike many aggregate functions, COUNT() never returns NULL. Therefore, COALESCE(COUNT(col)) is redundant.
• Column Nulls: Apply COUNT() to a NOT NULL column, or ensure the join condition guarantees non-null values.
• Optimization: For performance, especially with large datasets, consider pre-aggregating counts in a subquery before joining with the organizations table. This is particularly beneficial when counting most or all rows.

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