In the previous lessons, we have learnt how to join rows of different tables. If we want to join parts of table without giving connection to the other table then what will happen? Here, we have to use SQL OUTER JOIN.
The structure for outer join needs database for the performance of its function. In Oracle, we use “(+)” for the selection of all the rows in WHERE clause.
For example, we have to handle the given two tables,
Los Angeles $1550 Mar-05-2011
San Diego $300 Mar-07-2011
Los Angeles $400 Mar-08-2011
Boston $600 Mar-09-2011
West Los Angeles
West San Diego
East New York
Here, the requirement is to get the amount of sales of all the given stores. In regular join, we can not get New York as it is not included in the first table. So, we have to join the tables by OUTER JOIN.
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
Here, we have used the structure of Oracle.
Los Angeles $1950
San Diego $300
In the Geography table, there is no result for New York, so the value NULL will be given back. As a result, the column of sales for New York will remain NULL.