• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

regarding left outer join

 
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi all,
this is regarding left outer join.
suppose i have the below tables (took this example from wikipedia)

Employee Table
LastName DepartmentID
Rafferty 31
Jones 33
Steinberg 33
Robinson 34
Smith 34
John NULL

Department Table
DepartmentID DepartmentName
31 Sales
33 Engineering
34 Clerical
35 Marketing

now if i execute the below query on ORACLE database :

SELECT LastName, DepartmentName FROM employee, department WHERE employee.DepartmentID = department.DepartmentID(+)

then i will get the below result

LastName DepartmentName
Rafferty Sales
Jones Engineering
Steinberg Engineering
Robinson Clerical
Smith Clerical
John


i will not get any department name for john as i have used left outer join.

so now my question is that if i want to put some default department name for all those entries for which department name is null then is there any way to do that like if i want to mention department name as "no department" for john then how can i do that?

thanks,
pratz
 
Ranch Hand
Posts: 75
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
prateek sharmaa
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks a lot sudheer. it worked
 
reply
    Bookmark Topic Watch Topic
  • New Topic