[Prev] Thread [Next]  |  [Prev] Date [Next]

Re: [sqlite] why is count of rows in LEFT JOIN more than the rows inthe left table Puneet Kishor Mon Feb 20 09:00:11 2012

On Feb 20, 2012, at 9:34 AM, Igor Tandetnik wrote:

> Puneet Kishor <[EMAIL PROTECTED]> wrote:
>> But, why is #5 329743 and not 329686? Where are the extra 57 rows coming 
>> from? I thought a LEFT JOIN was supposed to include
>> *all* the rows from the left table with NULLs for the columns of the right 
>> table where there was no match. So, at most, the
>> resulting table would have as many rows as the rows in the left table. 
>> Certainly not more.  
> The conclusion doesn't follow from the premise. Yes, a row from the left 
> table is included in the resultset even if there are no matching rows in the 
> right table. Which means that the resultset would contain *at least* as many 
> rows as there are in the left table - but it certainly may contain more. For 
> each row in the left table that has N matching rows in the right table (with 
> N > 0), there will be N rows in the resultset.

Thanks for the great explanation. Now, the follow up question --

is there are way to force the query to return only the "first" match so the 
resulting query has only as many rows as the left table?

Puneet Kishor

sqlite-users mailing list