LEFT OUTER JOIN DOES NOT WORK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

LEFT OUTER JOIN DOES NOT WORK

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-06-04 : 10:47:52

Hi there. I have a question about "LEFT OUTER JOIN"

Table A is like
Column1 Column2 Value

Table B is like
Column1 ColumnC Value_C

The code is really simple.
Select A.Column1, A.Column2, B.ColumnC, A.Value, B.Value_C
FROM A LEFT OUTER JOIN B
ON A.Column1=B.Column1

Table A has ~60000 rows while the results only shows ~30000 rows.
I can not understand this. "LEFT OUTER JOIN" should retrieve all rows from left table, right?

Any one could help me explain this?
Thanks a lot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 11:01:51

Yes you are right.
The only thing is:
In your example you have A.Value_C in your select list but it should be B.Value_C

No, you're never too old to Yak'n'Roll if you're too young to die.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-06-04 : 11:04:16

Sorry,my bad. Have correct the previous post.

Could anyone tell me is there any possible exception in using LEFT OUTER JOIN?

Thanks

quote:
Originally posted by webfred

Yes you are right.
The only thing is:
In your example you have A.Value_C in your select list but it should be B.Value_C

No, you're never too old to Yak'n'Roll if you're too young to die.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-06-04 : 11:22:51

nobody can help?

quote:
Originally posted by mavershang

Sorry,my bad. Have correct the previous post.

Could anyone tell me is there any possible exception in using LEFT OUTER JOIN?

Thanks

quote:
Originally posted by webfred

Yes you are right.
The only thing is:
In your example you have A.Value_C in your select list but it should be B.Value_C

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 11:42:50

are you sure is table A having 60000 rows or is it B having 60000 rows ?

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 11:45:13

As I told you - yes you are right, it should retrieve all rows from left table.
But maybe your original query is different and we cannot see what is going on...

No, you're never too old to Yak'n'Roll if you're too young to die.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-06-04 : 11:52:34

I am pretty sure about that. Actually in the original code, I just select all rows from table A to check that.

quote:
Originally posted by khtan

are you sure is table A having 60000 rows or is it B having 60000 rows ?

KH
[spoiler]Time is always against us[/spoiler]

mavershang
Posting Yak Master

111 Posts

Posted - 2009-06-04 : 11:57:29

Here is the code:

----------------------------------------------
DECLARE @AlnID INT;

DECLARE @SeqID INT;

SET @AlnID=1

SET @SeqID=268794

SELECT ec.ColumnOrdinal1

,ec.ColumnOrdinal2

,AC1.ColumnNumber AS ColumnNumber1

,ec.taxid

,mi.pairCount

,mi.covary1

,mi.covary2

,mi.covary3

,mi.mixy

,mi.PairConservation

,mi.WCGUPct

,mi.WCGUInfo

,mi.WCGUInfoWtd

,mi.WCPct

,mi.WCInfo

,mi.WCInfoWtd

,ec.TotalEvents

,ec.CovaryEvents

,ec.TotalEventsLocal

,ec.CovaryEventsLocal

,ec.totalpairs

,ec.totalpairslocal

,ec.AA,ec.AC,ec.AG,ec.AU,ec.CA,ec.CC,ec.CG,ec.CU,ec.GA,ec.GC,ec.GG,ec.GU,ec.UA,ec.UC,ec.UG,ec.UU

INTO #T

FROM (([crwdb].[dbo].[cv_ec_1_Positive_top_mixys] ec LEFT OUTER JOIN [crwdb].[dbo].[cvtest_1_covary_results_filter] mi

ON ec.ColumnOrdinal1=mi.ColumnOrdinal1 AND ec.ColumnOrdinal2=mi.ColumnOrdinal2) JOIN [crwdb].[dbo].[AlignmentColumn] AC1

ON ec.ColumnOrdinal1=AC1.ColumnOrdinal)

WHERE AC1.AlnID=@AlnID

------- HERE #T CONTAIN ~60000 ROWS ------------
SELECT *

FROM #T

ORDER BY ColumnOrdinal1

-----------------------------------------------

-------- ONLY ABOUT 30000 SELECTED -----------
SELECT a.ColumnOrdinal1

,a.ColumnOrdinal2

,a.ColumnNumber1

,s.NucleotideNumber AS NucleotideNumber1

FROM #T a LEFT OUTER JOIN [crwdb].[dbo].[Sequence] s

ON a.ColumnNumber1=s.ColumnNumber

WHERE s.SeqID=@SeqID AND s.AlnID=@AlnID

ORDER BY ColumnOrdinal1, ColumnOrdinal2

DROP TABLE #T
---------------------------------------------------

Thanks for reply.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 12:02:51

It is because the WHERE-clause!

No, you're never too old to Yak'n'Roll if you're too young to die.

mavershang
Posting Yak Master

111 Posts

Posted - 2009-06-04 : 12:07:07

oh, what a dumb I am. I got it.

Thanks