• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

counting rows after joins varies

 
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maybe additional join conditions to filter out the unwanted rows...
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic