# Thread: Why does LEFT JOIN increase row count

1. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
post DDL for both tables

3. Registered User
Join Date
Mar 2007
Posts
628
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
•