Which Query in Right or Which in Wrong?

Question

2.33/5 (2 votes)

See more:

, +

Hello Everyone.
I have big confuse in two query. I want to count total records in a table which primary key is in another table. i user two types of query but both query giving me different result. so please help me

SQL

select COUNT(*) from first_tbl where isApprExam =1 and Code in (select schlcode from journals_tbl)

select COUNT(*) from regmaster inner join journals_tbl j on regmaster.Code=j.schlcode where regmaster.isApprExam =1

Please tell me why they giving different answer and which is right query.

Posted 3-Nov-15 0:41am

Divyam Sharma

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2015-11-03T10:01:00

If a row in the first table can be joined to several rows in the second table your query will count every occurrence.
In the case of the IN clause every corresponding row will be counted only once.

So if you want the number of rows in first_tbl/regmaster that has a corresponding row in journals_tbl you should use IN or EXISTS

jaket-cp · Answer 2 · 2015-11-04T05:10:00

Solution 2 answers your question quite nicely.

But here are additional SQL count examples which may make it clearer:

SQL

--taking regmaster and first_tbl to be the same table

select COUNT(distinct regmaster.Code) 
from regmaster 
inner join journals_tbl j 
	on regmaster.Code = j.schlcode 
where regmaster.isApprExam = 1
;
--should give the same count as
select COUNT(*) from first_tbl where isApprExam =1 and Code in (select schlcode from journals_tbl)



select COUNT(*) 
from journals_tbl 
where Code in (select schlcode from regmaster where isApprExam = 1)
;
--should give the same count as
select COUNT(*) from regmaster inner join journals_tbl j on regmaster.Code=j.schlcode where regmaster.isApprExam =1

Hopefully I got those right, and if so hope it helps you out in understanding.

sanjay243657 · Answer 3 · 2015-11-03T00:48:00

Solution 1

SQL

Hi,

Don't use cont(*) its decrease the performance.

select COUNT(1) from regmaster inner join journals_tbl j on regmaster.Code=j.schlcode where regmaster.isApprExam =1

Posted 3-Nov-15 0:48am

sanjay243657

Comments

Divyam Sharma 3-Nov-15 7:41am

ok.. but tell me which query is right and why?

Jörgen Andersson 3-Nov-15 15:46pm

That's simply wrong.
Count(*) is specifically defined in SQL92 to return the cardinality of the query.
And counting the rows where 1 is not null is less efficient than just counting the rows.
That said, the optimizer would realize that it's the same thing and give you the same plan.

Richard Deeming 4-Nov-15 11:54am

Count(1) and Count(*) are identical. See this StackOverflow answer[^] for a good explanation.

Answer reported as "Inaccurate/misleading".