Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

regarding left outer join

 
prateek sharmaa
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • 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
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
prateek sharmaa
Ranch Hand
Posts: 38
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks a lot sudheer. it worked
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic