Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Unanswered: Why does LEFT JOIN increase row count

    I am a newb when it comes to left joins and I am struggling to grasp why the number of rows returned by this query goes through the roof when I add a left join to it. Any help is sincerely appreciated.
    Code:
    SELECT
        a.col1,
        b.col2,
    FROM
        a
    LEFT JOIN
        b
    ON
        b.col3 = co3
    If I comment out the LEFT JOIN then the statement returns 118 rows. If I leave it in then I get upwards to 100,000 rows.

    My understanding of left joins is that if there are 15 rows (say) in b which have a value of Col3 that appears in a row in a then 15 rows will be returned which are duplicates of the row in a but each have different values for the other columns of b. If this was the case for every row then the result would be (118 x 15) = 1770 rows.

    What I'm stuck on is that Col3 is the key of b so there are either one or zero rows in it with each value of Col3 found in a. There are many rows in a with the same value of col3 but I am struggling to see how this could cause the row count to rise to hundreds of thousands. Any help is greatly appreciated by a newb.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for both tables
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    In main opinion, the main problem is, that the join condition does not join at all. You are describing the behaviour of query
    Code:
    SELECT a.col1, b.col2
    FROM a CROSS JOIN b
    WHERE b.col3 = co3
    The same would probably happen if you moved the condition on B.COL3 to WHERE clause (and join with expression which is always true, e.g "1=1"). Hard to tell whether this "join without join condition" should be treated as cross join.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •