Count Row

Question

Count Row

Sezam

Ten Centuries

Points: 1232
More actions
October 16, 2010 at 5:51 am

#225099

Hi Forum,
I've to tables with one-to-many relationships (T1 and N2)
Current queries giving me different results which is making me confuse.
select COUNT(*) from N2
Result:122263
select COUNT(*) from N2
inner join T1 on T1.DOCLINKID=N2.DOCLINKID
Result:122283
select COUNT(*) from N2
left join T1 on T1.DOCLINKID=N2.DOCLINKID
Result:122316
select COUNT(*) from N2
right join T1 on T1.DOCLINKID=N2.DOCLINKID
Result:122786
Select COUNT (*) from N2
where N2.DOCLINKID in (select DOCLINKID from T1)
Result:122230
There are 5 null DOCLINKID in T1. I really confuse in how to analyzing them.
How can I retrieve real numbers?
N2 is a master table.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

Dehqon D. (10/16/2010)
Hi Forum,
I've to tables with one-to-many relationships (T1 and N2)
Current queries giving me different results which is making me confuse.
select COUNT(*) from N2
Result:122263

There are 122263 records in N2

select COUNT(*) from N2
inner join T1 on T1.DOCLINKID=N2.DOCLINKID
Result:122283

There are 122283 records in T1 that have a parent in N2

select COUNT(*) from N2
left join T1 on T1.DOCLINKID=N2.DOCLINKID
Result:122316

There are 122316 records in T1 that have a parent in N2, OR no record in T1 for a parent in N2

select COUNT(*) from N2
right join T1 on T1.DOCLINKID=N2.DOCLINKID
Result:122786

There are 122786 records in N2 that have a parent in T1, OR no record in N2 for a parent in T1 (which means that you don't have a Foreign Key constraint set up between the tables)

Note: this can also be written as

SELECT COUNT(*) FROM T1

LEFT JOIN N2 ON N2.DOCLINKID = T1.DOCLINKID

Select COUNT (*) from N2
where N2.DOCLINKID in (select DOCLINKID from T1)
Result:122230

There are 122230 records in N2 where there is at least one child record in T1

There are 5 null DOCLINKID in T1. I really confuse in how to analyzing them.
How can I retrieve real numbers?
N2 is a master table.

Does this help you understand it?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2