how to use outer join in SQL server

[h3nd]

Hi guys,

I was just wondering how to use 'outer join' in SQL SERVER. I've tried this one, but I don't think it's working.

select ldn.* 
from dbo.ldnfund ldn
right outer join dbo.nyfund ny 
on ldn.Ccy = ny.Ccy

Any help ??
Thanks guys

 

[bborissov]

What do you mean it isn't working? How you now that if you get only fields from ldnfund in result set?

select *
from dbo.ldnfund ldn
right outer join dbo.nyfund ny
on ldn.Ccy = ny.Ccy

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[h3nd]

Yeah,

the table in ldnfund actually has more compare to nyfund. So I wanna find the records that exist in table "ldnfund" but not exist in "nyfund". That's all.

But in my first query, they came up with all records in ldnfund. I tried inner join, it worked, but not for the outer join.

 

[Welshbird]

Do you not want a LEFT outer join in this case?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[bborissov]

select *
from dbo.ldnfund ldn
LEFT join dbo.nyfund ny
on ldn.Ccy = ny.Ccy
WHERE ny.Ccy IS NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Welshbird]

That's what I meant. Thanks Boris - got called away and didn't have time to add it in.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[r937]

RIGHT OUTER JOIN is logically equivalent to LEFT OUTER JOIN, and many people make it a habit never to use RIGHT OUTER JOINs, ever

boris, i would return columns only from the left table if you're looking for unmatched rows...

select [b]ldn.*[/b]
  from dbo.ldnfund ldn
left outer
  join dbo.nyfund ny
    on ny.Ccy = ldn.Ccy
 where ny.Ccy is null

r937.com | rudy.ca

 

[h3nd]

Hi Boris,

Thanks your coding is working great.

r937,

I've tried change left to right, but the result was not the same.
The result returns with no rows.