• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

outer join in DB2

 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would like to use outer join operator in my where clause. how can I do?

I know it's a + symbol but have no idea how to use in where clause.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could try the ANSI join syntax (for a left outer join):

I don't know DB2, but Oracle's proprietory outer join syntax looks like this:

I wouldn't recommend using the proprietory syntax if you have an alternative (it's not portable).

Jules
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shan,
If the syntax Jule's posted works, use it. If not db2 syntax is:

select fields
from table1 full outer join table2
on table1.field1 = table2.field1

You can substitute "left" or "right" for the keyword "full."
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's the ANSI join syntax. I believe the "OUTER" keyword is optional.

Jules
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So it is. I guess I didn't read the example.

So that means DB2 does support the ANSI syntax and it should be used.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic