Search code examples
sqlpostgresqlcountcross-join

Separate subselects to get multiple counts?

I have this query:

SELECT  
(SELECT COUNT(media_id) FROM likes WHERE like_state = true AND media_id = ?) AS likes, 
(SELECT COUNT(media_id) FROM likes WHERE like_state = false AND media_id = ?) AS dislikes, 
(SELECT media_views FROM media_thumbnail WHERE media_id = ?) AS views;

It works fine but I'm not sure if this is the best way. Any recommendations?

I don't think this requires any explanation, but... It counts how many likes, or dislikes are in function of the like_state (true = like, false = dislike) and returns the view count of the associated video.

Solution

  • It's definitely faster to use a single SELECT with two counts on table likes instead of two separate SELECT:

    SELECT COUNT(    like_state OR NULL) AS likes
     , COUNT(NOT like_state OR NULL) AS dislikes
     ,(SELECT media_views FROM media_thumbnail WHERE media_id = ?) AS views
FROM   likes
WHERE  media_id = ?;

    There are corner-case differences between this query with an uncorrelated subquery, your original query with two implicit CROSS JOIN (a comma , in the FROM list is almost identical to a CROSS JOIN) as well as Gordon's answer with a CROSS JOIN to the last subquery.

    The first two subqueries always return a row, so a CROSS JOIN cannot eliminate the result. But the third query might not find the given media_id in table media_thumbnail and return no row. A CROSS JOIN to that results in no row for the whole query.

    My query with a subquery in the SELECT list converts no row to a null value and still returns a result row. The same could be achieved with LEFT JOIN ... ON true:

    My subquery in the SELECT raises an exception if it returns more than one row - which cannot happen if media_thumbnail.media_id is defined UNIQUE or PRIMARY KEY, and probably should not happen in any case.

    About conditional counts:

    The pretty and clean syntax would be with a FILTER clause:

    SELECT COUNT(*) FILTER (WHERE     like_state) AS likes
     , COUNT(*) FILTER (WHERE NOT like_state) AS dislikes
...

    A bit more verbose, but may be easier to read. Performance is basically the same.