CODEWITHSUNDEEP
sqlsql-servert-sql
Muckeypuck
Muckeypuck

Reputation: 559

I expect these 2 sql statements to return same number of rows

In my mind these 2 sql statements are equivalent.

My understanding is:

the first one i am pulling all rows from tmpPerson and filtering where they do not have an equivalent person id. This query returns 211 records.

The second one says give me all tmpPersons whose id isnt in person. this returns null.

Obviously they are not equivalent or theyd have the same results. so what am i missing? thanks

select p.id, bp.id
From person p
right join(
    select distinct id
    from tmpPerson
) bp
on p.id= bp.id
where p.id is null

select id
from tmpPerson
where id not in (select id from person)

I pulled some ids from the first result set and found no matching records for them in Person so im guessing the first one is accurate but im still surprised they're different

Upvotes: 0

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I much prefer left joins to right joins, so let's write the first query as:

select p.id, bp.id
From (select distinct id
      from tmpPerson
     ) bp left join
     person p
     on p.id = bp.id
where p.id is null;

(The preference is because the result set keeps all the rows in the first table rather than the last table. When reading the from clause, I immediately know what the first table is.)

The second is:

select id
from tmpPerson
where id not in (select id from person);

These are not equivalent for two reasons. The most likely reason in your case is that you have duplicate ids in tmpPerson. The first version removes the duplicates. The second doesn't. This is easily fixed by putting distincts in the right place.

The more subtle reason has to do with the semantics of not in. If any person.id has a NULL value, then all rows will be filtered out. I don't think that is the case with your query, but it is a difference.

I strongly recommend using not exists instead of not in for the reason just described:

select tp.id
from tmpPerson tp
where not exists (select 1 from person p where p.id = tp.id);

Upvotes: 2

Valli
Valli

Reputation: 1450

select id
from tmpPerson
where id not in (select id from person)

If there is a null id in tmp person then they will not be captured in this query. But in your first query they will be captured. So using an isnull will be resolve the issue

where isnull(id, 'N') not in (select id from person)

Upvotes: 1

Related Questions