SQL Select/Join Statement - Select when there is no Join?

Question

So Ive be using this select statement:

select runners.name, runners.age, sum(races.laptime) as totallaptime
from runners join races on runners.runnerid = races.runnerid
where runners.region = "region001"
group by runners.runnerid

This lists all the runners from a specific region and gives a total of all their lap times.

Unfortunately this misses out all the runners in that region who have no lap times yet (presumably because they have no entries in the races database).

How can I adjust this select statement to get it to include all the runners in a specific region, and list a 0 for totallaptime if they havent got any entries in the races database?

(Sorry for the vague question title, I couldnt think of any way of summarizing this question)

TIA

Answer 1

Use a LEFT JOIN:

select runners.name, runners.age, coalesce(sum(races.laptime),0) as totallaptime
from runners 
left join races on runners.runnerid = races.runnerid
where runners.region = "region001"
group by runners.runnerid

A LEFT JOIN selects all rows of the left table, returning results from the right table if they exist, or a single row with NULL for their values if they do not.

Note: I have added a COALESCE to the statement because summing a NULL will result in NULL; this will change these values into a 0.