CODEWITHSUNDEEP
sqlsql-serverexcept
Sandeep Sood
Sandeep Sood

Reputation: 11

Difference in these two SQL queries in SQL Server,

I am trying to find those IDs which have not paid any fee.

There are some duplicate IDs with some records with FeePaid is not null and some records with FeePaid as null.

Why do these two queries return different output ?

select distinct ID 
from MyTable 
where FeePaid is null  
  and ID not in (select distinct ID from MyTable 
                 where FeePaid is not null)

and

select distinct ID 
from myTable 
where FeePaid is null  

except

select distinct ID 
from MyTable 
where FeePaid is not null

Upvotes: 1

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

The second should do what you want. I might also suggest:

select id
from mytable
group by id
having max(feepaid) is null;

The first query uses not in with a subquery, which I strong discourage. One possibility is that id is NULL. If it is ever NULL, then then the not in condition returns NULL and the query returns no rows.

To do what you want, I would suggest using not exists:

select distinct t.ID
from MyTable t
where FeePaid is null and
      not exists (select 1
                  from MyTable t2
                  where t2.id = t.id and t2.FeePaid is not null
                 );

Upvotes: 1

Related Questions