Two SQL LEFT JOINS produce incorrect result

by

Joins are processed left to right (unless parentheses dictate otherwise). If you LEFT JOIN (or just JOIN, similar effect) three groceries to one user you get 3 rows (1 x 3). If you then join 4 fishmarkets for the same user, you get 12 (3 x 4) rows, multiplying the previous count in the result, not adding to it, like you may have hoped for.
Thereby multiplying the visits for groceries and fishmarkets alike.

You can make it work like this:

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;

To get aggregated values for one or few users, correlated subqueries like @Vince provided are just fine. For a whole table or major parts of it, it is (much) more efficient to aggregate the n-tables and join to the result once. This way, we also do not need another GROUP BY in the outer query.

grocery_visits and fishmarket_visits are NULL for users without any related entries in the respective tables. If you need 0 instead (or any arbitrary number), use COALESCE in the outer SELECT:

SELECT u.id
     , u.account_balance
     , COALESCE(g.grocery_visits   , 0) AS grocery_visits
     , COALESCE(f.fishmarket_visits, 0) AS fishmarket_visits
FROM   ...

More Related Contents:

  1. Select rows which are not present in other table
  2. Aggregate columns with additional (distinct) filters
  3. PostgreSQL: running count of rows for a query ‘by minute’
  4. Create a pivot table with PostgreSQL
  5. Query with LEFT JOIN not returning rows for count of 0
  6. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  7. Return multiple columns of the same row as JSON array of objects
  8. How to include “zero” / “0” results in COUNT aggregate?
  9. GROUP BY + CASE statement
  10. Best way to count rows by arbitrary time intervals
  11. Selecting data into a Postgres array
  12. Compute percents from SUM() in the same SELECT sql query
  13. Get the distinct sum of a joined table column
  14. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  15. Aggregate a single column in query with many columns
  16. Multiple array_agg() calls in a single query
  17. Postgres window function and group by exception
  18. Count cumulative total in Postgresql
  19. Truncate timestamp to arbitrary intervals
  20. Select first row in each GROUP BY group?
  21. How to use RETURNING with ON CONFLICT in PostgreSQL?
  22. Optimal way to concatenate/aggregate strings
  23. How do I force Postgres to use a particular index?
  24. Query for element of array in JSON column
  25. Casting NULL type when updating multiple rows
  26. Best way to delete millions of rows by ID
  27. How to read the contents of an .sql file into an R script to run a query?
  28. INSERT COMMAND :: ERROR: column “value” does not exist
  29. Counting the number of occurrences of a substring within a string in PostgreSQL
  30. How to detect query which holds the lock in Postgres?

Leave a Comment