This time I would like to explain outer joins in Oracle. In a different article I already described what inner joins are and how to use them in Oracle SQL. This time we will step forward and take a closer look at the second important join type: LEFT and RIGHT OUTER JOINS.
When we talked about inner joins I explained that two tables are connected by one or more columns called the join condition. When these columns of both tables match the rows are connected (i.e. joined) and returned in the result set. When they don’t match then they are not returned. That’s where the outer join comes into play. This join type returns all rows of one of the two tables and connects the rows of the other table where it is possible. If not then these columns in the result are NULL. Depending of which of the two tables all rows are returned it is called a LEFT or RIGHT OUTER JOIN. There is also a FULL OUTER JOIN. But that is not described in this article.
Let’s arrange a little example for that:
Our SQL looks like this:
FROM TBL_CUSTOMER c LEFT JOIN TBL_SALES s ON c.ID=s.CUSTOMER_ID
The result-set is this:
As you can see Oracle takes all rows of TBL_CUSTOMER and for each row it tries to find a match in TBL_SALES. Where it is found it is taken, where not the result is NULL. Exchange LEFT with RIGHT in the SQL script and the result looks this way:
Now it is the other way round. All rows of TBL_SALES are returned and only the matches in TBL_CUSTOMER.
With the next example you can easily find the rows that have no matches in TBL_CUSTOMER, maybe for quality reasons or whatever:
FROM TBL_CUSTOMER c RIGHT JOIN TBL_SALES s ON c.ID=s.CUSTOMER_ID
WHERE c.ID IS NULL
With that you just receive the last row of TBL_SALES.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!