Select strings that do not have foreign keys associated

Question

I have 2 group tables and people

People have GroupId associated with Group.GroupId (primary key)

How can I choose groups that don’t have people? in t-sql and in linq

Thank you

Answer 1

Update

I launched four different ways to do this using SQL Server 2005 and included an execution plan.

-- 269 reads, 16 CPU SELECT * FROM Groups WHERE NOT EXISTS ( SELECT * FROM People WHERE People.GroupId = Groups.GroupId ); -- 249 reads, 15 CPU SELECT * FROM Groups WHERE ( SELECT COUNT(*) FROM People WHERE People.GroupId = Groups.GroupId ) = 0 -- 249 reads, 14 CPU SELECT * FROM Groups WHERE GroupId NOT IN ( SELECT DISTINCT GroupId FROM Users ) -- 10 reads, 12 CPU SELECT * FROM Groups LEFT JOIN Users ON Users.GroupId = Groups.GroupId WHERE Users.GroupId IS NULL 

Thus, the latter, although perhaps the most widely read of the four, works best.

This is somehow unexpected for me, and to be honest, I still prefer the WHERE NOT EXISTS syntax, because I think it is more explicit - it reads exactly the way you are trying to do.

Answer 2

My preferred method is to combine with the left anti-sex:

 SELECT g.* FROM Groups g LEFT JOIN People p ON g.GroupID = p.GroupID WHERE p.GroupID IS NULL 

I find it the most intuitive, flexible and efficient.

I wrote an entire article about various search strategies for finding missing data - see here if you are interested.

Answer 3

I think the simplest solution is:

 SELECT * FROM GROUPS WHERE GroupId NOT IN (SELECT DISTINCT GroupId FROM People)