Query with LEFT JOIN not returning rows for count of 0

by

Fix the LEFT JOIN

This should work:

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;

You had a LEFT [OUTER] JOIN but the later WHERE conditions made it act like a plain [INNER] JOIN.
Move the condition(s) to the JOIN clause to make it work as intended. This way, only rows that fulfill all these conditions are joined in the first place (or columns from the right table are filled with NULL). Like you had it, joined rows are tested for additional conditions virtually after the LEFT JOIN and removed if they don’t pass, just like with a plain JOIN.

count() never returns NULL to begin with. It’s an exception among aggregate functions in this respect. Therefore, COALESCE(COUNT(col)) never makes sense, even with additional parameters. The manual:

It should be noted that except for count, these functions return a null value when no rows are selected.

Bold emphasis mine. See:

count() must be on a column defined NOT NULL (like e.id), or where the join condition guarantees NOT NULL (e.organisation_id, e.item_template_id, or e.used) in the example.

Since used is type boolean, the expression e.used = true is noise that burns down to just e.used.

Since o.name is not defined UNIQUE NOT NULL, you may want to GROUP BY o.id instead (id being the PK) – unless you intend to fold rows with the same name (including NULL).

Aggregate first, join later

If most or all rows of exam_items are counted in the process, this equivalent query is typically considerably faster / cheaper:

SELECT o.id, o.name AS organisation_name, e.total_used
FROM   organisations o
LEFT   JOIN (
   SELECT organisation_id AS id   -- alias to simplify join syntax
        , count(*) AS total_used  -- count(*) = fastest to count all
   FROM   exam_items
   WHERE  item_template_id = #{sanitize(item_template_id)}
   AND    used
   GROUP  BY 1
   ) e USING (id)
ORDER  BY o.name, o.id;

(This is assuming that you don’t want to fold rows with the same name like mentioned above – the typical case.)

Now we can use the faster / simpler count(*) in the subquery, and we need no GROUP BY in the outer SELECT.

See:

More Related Contents:

  1. Select rows which are not present in other table
  2. Two SQL LEFT JOINS produce incorrect result
  3. Fast way to discover the row count of a table in PostgreSQL
  4. Compute percents from SUM() in the same SELECT sql query
  5. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  6. Conditional SQL count
  7. How do I speed up counting rows in a PostgreSQL table?
  8. Run a query with a LIMIT/OFFSET and also get the total number of rows
  9. Query for count of distinct values in a rolling date range
  10. Optimize GROUP BY query to retrieve latest row per user
  11. How to implement a many-to-many relationship in PostgreSQL?
  12. Check if value exists in Postgres array
  13. How to use ANY instead of IN in a WHERE clause?
  14. Is there a shortcut for SELECT * FROM?
  15. sql statement error: “column .. does not exist”
  16. Return multiple columns of the same row as JSON array of objects
  17. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  18. Getting all Buildings in range of 5 miles from specified coordinates
  19. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  20. Call a set-returning function with an array argument multiple times
  21. Fastest check if row exists in PostgreSQL
  22. Discard millisecond part from timestamp
  23. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  24. How to count the number of occurrences of a character in an Oracle varchar value?
  25. SQL column reference “id” is ambiguous
  26. How to use multiple CTEs in a single SQL query?
  27. IN Clause with NULL or IS NULL
  28. Postgres LEFT JOIN with WHERE condition
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Is there a postgres CLOSEST operator?

Leave a Comment