Joining one table multiple times with null FKs

Question

In a situation where I have two tables:

• Games(id, player1Id, player2Id)
• Players(id, gender)

And I would like to select all of the games and the player's gender, how would I get around null playerIds?

From this explanation, I would think the answer would be:

SELECT Games.id, p1.gender, p2.gender
FROM Games JOIN Players p1 on player1Id = p1.id 
JOIN Players p2 on player2Id = p2.id

However, this does not return any game that has either player1Id or player2Id as null. Is there a way to show all games with players connected when possible?

Thanks.

Answer 1

You need to use a LEFT JOIN:

   SELECT g.id, p1.gender, p2.gender
     FROM Games g
LEFT JOIN Players p1 ON p1.id = g.player1Id
LEFT JOIN Players p2 on p2.id = g.player2Id

Here's a really good link that visual demonstrates the different JOINs