PostgreSQL execute a default query when no row are returned I need to calculate the average score of a list of userId.

I have a table user_place_score:

(user_id, place_id, score)


Sometimes I do not have the score (no row for (user_id,place_id) in the table) so I need to fetch a "default" score from the table default_place_score:

(place_id, score)


Is is possible in PostgreSQL to do this in one query? For a given list of users and one place_id, select all their scores in the user_place_score and if no row is found then take the score found if the default_place_score table?

Basically I want to avoid doing this kind of stuff:

for (Integer userId : userIds) {
  score = (query to fetch the score for this userId)
  if (score == null)
    defaultScore = (query to fetch the default score for this PlaceId)

  total = total + defaultScore
}

//return the average
return total/numberOfUser
You could use COALESCE() .. it returns the first non-null value in the list passed to it..

COALESCE((QUERY TO GET SCORE FOR USERID),(QUERY TO GET DEFAULT SCORE))

EDIT

oh - you added "list" requirement ... see @ypercube's answer, then.

EDIT2

If the place_ids exist in a separate table, and they only DON'T EXIST in the table you're querying to get scores (which, imo, would be considered a 'good design decision'), then you should include that table in your query .. COALESCE() as used in @ypercube's answer should work fine in that case.

SELECT 
  u.user_id,
  u.place_id,
  COALECSE(u.score, d.score) AS score
FROM
  place_list AS pl
LEFT JOIN
  user_place_score AS u ON pl.place_id = u.place_id
LEFT JOIN 
  default_place_score AS d
    ON d.place_id = u.place_id ;


Above taken and edited from @ypercube's answer.