SQLTeam.com | Weblogs | Forums

Help with Except

I don't really understand Except very well.

This statement, when run using huge tables is very slow. I think I should be using Except instead.
Select ssn, name, address from PeopleTable1
where
ssn not in (Select ssn from PeopleTable2 where year = '2020')

When I try using Except, I get an error saying that both Select statements have to have the same number of items listed. But in this case they don't. I just want to do what's shown above.
Thank you!

If you don't want to compare the name and address, then you should use the "NOT EXISTS" form rather than NOT IN.

Select ssn, name, address
from PeopleTable1 PT1
where not exists(Select 1 from PeopleTable2 PT2 where PT2.year = '2020' AND PT2.ssn = PT1.ssn)

If PeopleTable2 is large, create a covering index first, keyed on (year, ssn), on the PT2 table.

2 Likes

Is your Issue got solved or not. Let us know about it.