MySQL Forums
Forum List  »  General

Null records create vastly different results
Posted by: Jim Van Overschelde
Date: April 05, 2022 10:25AM

I am flummoxed why I am getting vastly different results for these two queries when the second query simply eliminates one null record.

select count(distinct ID2), syear
from db.table1 a
where a.id2 is not null
and a.id2 not in
(select b.id2
from db.table2 b
group by b.id2
)
group by a.syear;
-- This query produces ZERO records. Subquery has 399,980 ID2s

select count(distinct ID2), syear
from db.table1 a
where a.id2 is not null
and a.id2 not in
(select b.id2
from db.table2 b
where b.id2 is not null
group by b.id2
)
group by a.syear;
-- This query produces counts totally 169k. Subquery has 399,979 ID2s

Why does including the one null record in the subquery produce no records in the main query?

Options: ReplyQuote


Subject
Written By
Posted
Null records create vastly different results
April 05, 2022 10:25AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.