Problem solve Get help with specific problems with your technologies, process and projects.

Different queries, different counts

I don't know why I am getting different counts for these two queries. I think both queries should return the same record count.

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

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.