Left Outer Joins

Im writing this simple post to document left outer join and displess confusion around usage:

1. Syntax one (supported by oracle):

SELECT *
FROM employee LEFT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID

In above example, query will fetch all records from employee, even if department value is missing as it is specified on the "left"

If above is changed to below:

SELECT *
FROM department LEFT OUTER JOIN employee
  ON employee.DepartmentID = department.DepartmentID

Now, the above will fetch all records from department even if employees are not present in that department. The above query and query below have same results:

SELECT *
FROM department RIGHT OUTER JOIN employee
ON employee.DepartmentID = department.DepartmentID

2. Alternate Syntax (deprecated - may not work in future releases):

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID(+)
This will fetch all records from employee, although the "+" is specified next to department. I think oracle decided to deprecate this as its non intuitive? We add + to department but query returns all records from employee? Non intuitive.

Note, Oracle BI does not use deprecated syntax, it is shown just for trouble shooting. To add outer joins in OBIEE RPD, navigate to logical layer and edit the join. This is shown below:

 

Now here is something to think about: developers often add outer join and then add prompts on the dashboard. As soon as prompts are applied, outer join would not work.

 

For example if you want to join sales fact to a product cost table, which may or may not have a cost type value for all cost types. This can be achieved by using an outer join. Next, if you want user to be able to select cost type from the prompt,  this will now result in outer join to not work.

 

There are some work arounds for this too, for example use an opaque view in physical layer, specify a variable to filter for a cost type, and then in dashboard prompt, update value of the variable to change cost types. This ensures that outer join will keep working.