SQL Server NOT EXISTS efficiency

Question

I have two tables Card and History with a one-to-many relationship: one card can have one or many histories. Card has a CardId column, and History has a CardId and a StatusId column.

I want a SQL script which selects the only cards which have no one history with StatusId=310.

This is what I've tried.

SELECT 
    C.CardId 
FROM 
    Card C
WHERE NOT EXISTS (SELECT * 
                  FROM History H
                  WHERE H.CardId = C.CardId AND H.StatusId = 310)

But I want to know if there is an efficient way.

Thanks in advance.

Answer 1

To answer your question about whether there is a more efficient way:

Short answer: No, not exists is most likely the most efficient method.

Long answer: Aaron Bertrand's article with benchmarks for many different methods of doing the same thing Should I use not in, outer apply, left outer join, except, or not exists? Spoiler: not exists wins.

I would stick with your original code using not exists, but Aaron's article has many examples you can adapt to your situation to confirm that nothing else is more efficient.

select c.CardId 
  from Card c
  where not exists(
    select 1
      from History h
      where h.CardId=c.CardId 
        and h.StatusId=310
             )