I have a teams table and a players table and I’m wanting to find all teams that do not have players associated with them. The players table is associated via a
team_id
column. I’m using Ruby on Rails for this so I have a Team and a Player model.
Answer
It may perform better doing a left join:
SELECT
teams.*
FROM teams
LEFT JOIN players ON (teams.id = players.team_id)
WHERE
players.team_id IS NULL
Or using ARel (thanks to JasonKing’s comment):
Team.includes(:players).where('players.team_id IS NULL')
Attribution
Source : Link , Question Author : Ryan Bigg , Answer Author : Ryan Bigg