I don't know why I am getting different counts for these two queries:
Query 1 returns 1112 rows -- select count(mkt_part) from devmastertemp a, primary_view b where a.mkt_part = b.mkt_part
Query 2 returns 1080 rows -- select count(mkt_part) from devmastertemp a where exists (select 1 from primary_view b where a.mkt_part = b.mkt_part)
I think both queries should return the same record count.
Those queries would return the same count, if every devmastertemp row were related to no more than one primary_view row. It looks, however, as though there are actually 32 "duplicates" in the primary_view table.
The first query counts the number of matched devmastertemp mkt_part values that are present in the INNER JOIN. Suppose there is some devmastertemp mkt_part which has several matching primary_view rows. I used the word "duplicate" to describe these, but of course they could be legitimately different rows, except for the fact that they are all related to that particular devmastertemp mkt_part. So in the join results, there will be one row per matched primary_view row. Thus that particular devmastertemp mkt_part value will be counted as many times as it has matching rows.
The second query also counts the number of devmastertemp mkt_part values, but this time using a WHERE EXISTS subquery. No matter how many matching primary_view rows a given single devmastertemp mkt_part has, the result of the EXISTS condition will be true as long as there is at least one. Thus each devmastertemp mkt_part will be counted only once.
In order to get the same counts, modify your first query as follows:
Query 1 -- select count(distinct mkt_part) from ...
This will now give you the same count as your WHERE EXISTS query. However, the WHERE EXISTS query would likely be faster.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.