Why Does My PostgreSQL LEFT JOIN Query Multiply Counts Instead of Preserving Them?

PostgreSQL LEFT JOIN: Unexpected Row Count Multiplication

A PostgreSQL 9.1 query using multiple LEFT JOINs unexpectedly multiplied row counts instead of preserving them. The query aimed to retrieve user IDs, account balances, grocery visit counts, and fishmarket visit counts. The flawed query resulted in incorrect counts, showing repeated values instead of the actual counts.

This issue arises from PostgreSQL's left-to-right JOIN processing order. Subsequent JOINs replicate rows from earlier joins, leading to count inflation. For example, if there were 3 grocery visits and 12 fishmarket visits, the result incorrectly showed 12 entries for each instead of the expected 3 and 12.

The solution involves pre-aggregating the visit counts before joining. This avoids the row multiplication problem. The corrected query is:

<code class="language-sql">SELECT u.id, u.account_balance, g.grocery_visits, f.fishmarket_visits
FROM users u
LEFT JOIN (
   SELECT user_id, count(*) AS grocery_visits
   FROM grocery
   GROUP BY user_id
) g ON g.user_id = u.id
LEFT JOIN (
   SELECT user_id, count(*) AS fishmarket_visits
   FROM fishmarket
   GROUP BY user_id
) f ON f.user_id = u.id
ORDER BY u.id;</code>

This revised query correctly returns the individual counts for grocery and fishmarket visits per user, resolving the multiplication issue. For instance, if a user had 3 grocery visits and 4 fishmarket visits, the output accurately reflects this, avoiding the erroneous repetition seen in the original query.

The above is the detailed content of Why Does My PostgreSQL LEFT JOIN Query Multiply Counts Instead of Preserving Them?. For more information, please follow other related articles on the PHP Chinese website!