The moose likes JDBC and Relational Databases and the fly likes regarding left outer join Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "regarding left outer join" Watch "regarding left outer join" New topic

regarding left outer join

prateek sharmaa
Ranch Hand

Joined: May 15, 2010
Posts: 38
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

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

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?

Sudheer Bhat
Ranch Hand

Joined: Feb 22, 2011
Posts: 75
prateek sharmaa
Ranch Hand

Joined: May 15, 2010
Posts: 38
thanks a lot sudheer. it worked
I agree. Here's the link:
subject: regarding left outer join
It's not a secret anymore!