• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Devaka Cooray
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Knute Snortum
  • Bear Bibeault
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Piet Souris
  • Ganesh Patekar
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Tim Holloway

counting rows after joins varies

 
Sheriff
Posts: 9099
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I thought that once I did an inner join that the resulting number of rows would be a limiting factor regardless of how many left outer joins I did afterwards.

and that seems to be true for the first few lines of this query


But when I include the last two lines of the above code, the row count increases. I don't understand why.
 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

I thought that once I did an inner join that the resulting number of rows would be a limiting factor



No. Adding a left outer join to a query will not decrease the number of rows returned. But it could increase the number or rows returned if the outer join condition results in a one-to-many match.
 
Marilyn de Queiroz
Sheriff
Posts: 9099
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there any way to make it stay the same number of rows as the table on the left? I know an inner join definitely won't work.
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe additional join conditions to filter out the unwanted rows...
 
Marilyn de Queiroz
Sheriff
Posts: 9099
12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, Scott. Sometimes I just can't see the forest for the trees.
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MdQ: counting rows after joins varies

Of course it can!

****

1. Also be extremely mindful of exactly what you're COUNT()ing on in situations such as this (particularly regarding NULLs).

For example, each of the following *can* result in a different count:

select count(*)
from a left join b
on a.id = b.id

select count(a.id)
from a left join b
on a.id = b.id

select count(b.id)
from a left join b
on a.id = b.id

select count(a.some_other_field)
from a left join b
on a.id = b.id

select count(b.yet_even_some_other_field)
from a left join b
on a.id = b.id

2. Be mindful of the fields you're grouping on.

Is there any way to make it stay the same number of rows as the table on the left?

Typically, count on some nonnullable field on the left table (often a key, and often one of the fields you're joining on, but not obligatorily).
[ September 21, 2006: Message edited by: Michael Matola ]
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!