Finding all records without associated ones

Question

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 1

You'd do that with a NOT EXISTS condition like so:

SELECT *
FROM teams
WHERE NOT EXISTS (SELECT 1 FROM players WHERE players.team_id = teams.team_id)

Answer 2

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')