Lets take a simple scenario. I want to generate a report that shows the sales by showroom, and the report should display all the showrooms in the report. For the showroom with no sales it should display the region with a NULL value for sales.
The usual join (also called as inner join) will not work in this scenario. We need what is called as an outer join. If you want to know more about outer joins you can checkout the Wikipedia link here.
To enable outer join you need to first set the ANSI92 parameter to Yes. This will change the query from the simple join to an an inner join syntax with from clause as shown below.
You can also enable the FILTER_IN_FROM parameter. This pushes the where clause of the join inside the from which reduces the number of records in the join condition and will greatly improve performance.
Now we can setup our outer join. As shown below we can setup the right outer join betwen the fact table and the showroom dimension table.
The effect of this is that all showrooms irrespective of whether they had a sale or not will be returned by the query.
So in conclusion while it is easy to setup outer joins in the universe, one should be very careful when using outer joins as it can result in a cartesian product of all rows especially when using full outer join.
