CODEWITHSUNDEEP
sqllinq
soso
soso

Reputation:

select rows that do not have any foreign keys linked

i have 2 tables Group and People

People has GroupId that is linked to Group.GroupId (primary key)

how can I select groups that don't have any people? in t-sql and in linq

thank you

Upvotes: 16

Views: 10768

Answers (3)

Matt Hamilton
Matt Hamilton

Reputation: 204229

Update

I've run four different ways to do this through SQL Server 2005 and included the 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

So the last one, while arguably the least readable of the four, performs the best.

That comes as something as a surprise to me, and honestly I still prefer the WHERE NOT EXISTS syntax because I think it's more explicit - it reads exactly like what you're trying to do.

Upvotes: 38

Roee Adler
Roee Adler

Reputation: 34000

I think the simplest solution is:

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

Upvotes: 2

Aaron Alton
Aaron Alton

Reputation: 23236

My preferred method is a left-anti-semi join:

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

I find it most intitive, flexible, and performant.

I wrote an entire article on various query strategies to search for the absence of data - have a look here if you're interested.

Upvotes: 8

Related Questions