Can you tell me why these yield different results

Doing this in SQL Developer and would have thought that the INNER JOIN would be the same as the 'IN' in the bottom statement...Obviously wrong but wanna know why? Im thinking the Date is somehow causing my problem? Because when I do an equal instead of an  '>=' the numbers are identical???

Select COUNT(*)
FROM CANC_REIN ca
INNER JOIN COVERAGE co ON ca.POLNBR = co.POLNBR
WHERE co.COV_EXPDT >= '04-JUN-09'
Order By ca.CARRID,ca.POLNBR,ca.COV_EFFDT,ca.SEQ asc;            

Returns 2873058

Select Count(*)
FROM CANC_REIN
Where POLNBR IN (Select POLNBR from COVERAGE WHERE COV_EXPDT >= '04-JUN-09')
Order By CARRID,POLNBR,COV_EFFDT,SEQ asc;

Returns 1412583
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can you have multiple POLNBR's?

IN will count 1 when they repeat:
select count(*) from dual where dummy in ('X','X','X','X');

The join should count every one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
That's it because there are multiple POLNBR records in the COVERAGE table and its counting every one. Nice job and Thanks

Another question to come in this matter
0
Walter RitzelSenior Software EngineerCommented:
Without knowing your model, it is hard to tell you for sure why. But what I'll say is this: you most probably have written your query trying to count the wrong main table. I mean, it seems that CANC_REIN table have just one instance of POLNBR, with no duplicates, while COVERAGE can have a POLNBR repeated. Because of that, when you join both with INNER JOIN, you have the total count in COVERAGE table, while when you use the IN, you are counting only the records in CANC_REIN table.
If you want the counts to match, the IN query should be something like this:
Select Count(*) 
FROM COVERAGE
Where POLNBR IN (Select POLNBR from CANC_REIN )
WHERE COV_EXPDT >= '04-JUN-09' 

Open in new window


And just a hint: in a COUNT query, ORDER BY does not help anything.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.