SQL Except vs Not In

1

I have a SQL statement which runs pretty slow, and I'm wondering if it will run faster if I use Except.
Except I can't figure out how to use Except. The SQL looks like this:

Select Name from Person where SSN not in (Select SSN from Paid)

Will this run faster if I use Except?

Thank you.

2

You can tested it :slight_smile:

select Name from Person as N where not exists(select * from Paid as p Where p.ssn = N.ssn)

1 Like
3

Well, lets see...
Mine ran in 1:41.

Yours ran in :01.

So, thank you!

4

You're welcome!

ps; ssn field it is a good candidate of having index on it, if it is possible

5

Agreed with the possible exception of a unique index or unique constraint to prevent duplication.

6

Would you mind converting another one for me? I tried to figure out the syntax myself but am getting an error and don't know how to fix it.

Here is a simplified version of my query:

Select Name from Person p
join Position q on q.Pos_Person_ID=p.person_ID
where
p.ssn not in (Select ssn from PAID where Station='523')

7

I believe this will work:

Select Name from Person p
join Position q on q.Pos_Person_ID=p.person_ID
where not exists(select * from PAID as PD where Station=‘523’ and PD.ssn = P.ssn )
8

What about:

select Name
from Person p
    join Position q on q.Pos_person_ID = p.person_ID
    left join Paid on Paid.ssn = p.ssn
where Paid.ssn is null