LEFT OUTER JOIN - 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

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-16 : 07:29:21

select * from ti_inv has 54 rows

Now I have the following code:
select * from ti_inv a left outer join F_AR b
on A.invno = b.DocNumber
where LEID = '21001'
and PeriodKey = '102'

This only generates 14 rows.

This is a LEFT OUTER JOIN so I would have thought that even
if there were no matches all 54 rows from ti_inv would have been received but with NULLS on the right.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-16 : 07:31:11

Is LEID or PeriodKey in ti_inv table? If so, your where clause is restricting what's returned from ti_inv.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tariq2
Posting Yak Master

125 Posts

Posted - 2011-09-16 : 07:39:58

yes, either of these fields exists in ti_inv.

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-16 : 07:41:25

There you go then, that's why it returns a subset.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2011-09-16 : 07:44:19

Compare


select *
from ti_inv a
where LEID = '21001'
      and PeriodKey = '102'

select * 
from ti_inv a
    left outer join F_AR b 
        on A.invno = b.DocNumber
where LEID = '21001'
      and PeriodKey = '102'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources