Left Outer Join not returning expected result

Question

I have a two tables: player and game. I would like to return a list of all players regardless of whether their team played or not. I would like the game_id if their team did play, otherwise substitute for a NULL.

I thought this would just be a LEFT OUTER JOIN, but it is only returning a list of players that actually played.

SELECT a.id, b.match_id 
FROM player a 
  LEFT OUTER JOIN game b ON a.team_id = b.home_team_id or a.team_id = b.away_team_id 
WHERE b.round = 1

I imagine this is basic stuff .. sorry.

Answer 1

Your where condition on the outer table turns the outer join back into an inner join because the where will only be true for values that are not null, but rows where no match is found in the outer joined table will have all null values.

You need to move your where condition into the join condition.

SELECT a.id, b.match_id 
FROM player a 
  LEFT OUTER JOIN game b 
     ON (a.team_id = b.home_team_id or a.team_id = b.away_team_id) 
    AND b.round = 1