Left joining two tables, if null default to 0

join, left-join, null, postgresql, sql

Solution

You use `coalesce()`:

select p.player_id, p.name, coalesce(w.wins , 0) as wins
from players p left join 
     (select winner, count(winner) as wins
      from outcomes
      group by winner
     ) w
     on p.player_id = w.winner;

This is ANSI standard SQL and will work in most databases, include MySQL and Postgres.

Problem

I am left joining a table and multiple subqueries in postgresql. In one of the first joins, I am joining a table with a list of player ids and names to a subquery selecting their associated wins. Some players however, have not yet had a win. Below is the query and the output: ``` select players.player_id, players.name, b.wins from players left join (select winner, count(winner) as wins from outcomes group by winner) b on players.player_id = b.winner; player_id | name | wins -----------+-------------------+------ 500 | person 1 | 3 501 | person 2 | 502 | person 3 | 1 ``` I would like to know how I can automatically have this query default to wins being 0 if the value in that column comes up as null. I would like the above table to look like this: ``` player_id | name | wins -----------+-------------------+------ 500 | person 1 | 3 501 | person 2 | 0 502 | person 3 | 1 ``` - I plan to make this a view, so I don't see a way to make the column's default value 0. - I've had a look around on stackoverflow and google, but there seems to be a lot of responses for mysql but not postgresql - Those responses using postgresql seem very complex to me. - I hope this will also assist other having similar difficulties.

Original source