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

Something like this:

select * from teams
where team_id not in (select distinct team_id from players)

Answer 2

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 3

You should generally be able to do an outer join from parent to child and check for a null value in a field in the child that is not null-able. This is generally faster than a 'not in' or 'not exists'. This may not work on all databases.

Answer 4

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