Join behavior question

[djj55]

Hello, SQL2008R2
I may have asked this before but here we go:
If I have a LEFT join that needs to INNER join to a table the number of records returned change.

SELECT A.* 
FROM A 
INNER JOIN B ON A.V1 = B.V1
LEFT JOIN C ON A.V2 = C.V2
-- gives 3 records
INNER JOIN D ON C.V3 = D.V3
-- by adding this one line it gives 1 record

I thought a left join would return all records for the left side?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

I fixed a bug that one of my developers had that was identical to this (except for the table names).

Think of it this way... the C and D tables are inner joined so only matching rows are returned from that join. Then C is joined to A as a left join but the damage is already done.

To fix this problem, change the join between C and D to a left join.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

hi,

Yea, but how many of the 3 rows have C values, since C is outer joined to A???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[djj55]

Thank you, I did not think to use a left join as I was trying to limit the C records. I used a sub query (could have used a CTE).

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!