I have a table full of player names:

I also have a table full of player penalties:

I have to list all of the players names, player numbers, and the number of penalties that they've incurred.

To do that, I'm using this code:

select players.playerno, name, ifnull(`Penalty count`, 0) as `Penalty count` from players
left join
(select players.playerno, count(players.playerno) as `Penalty count` from players, penalties
where players.playerno = penalties.playerno
group by players.playerno) as `query1`
on players.playerno = query1.playerno
order by playerno

The code works. Here's the output table:

It seems like a lot of code just to merge two tables together. Is there a better way to do it?

I've also read online that keeping the NULL is generally a better practice than changing it to zero, but would that apply in this case, since it's supposed to be the total number of penalties? It seems like NULL would give the impression of "no data" or "unknown data", but in this case the number is known to be zero.