Let's assume that we have the following two tables:-
Table Store_Information
| store_name | Sales | Date |
| Los Angeles | $1500 | Jan-05-1999 |
| San Diego | $250 | Jan-07-1999 |
| Los Angeles | $300 | Jan-08-1999 |
| Boston | $700 | Jan-08-1999 |
Table Geography
| region_name | store_name |
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
and we want to find out the sales amount for all of the stores. If we do a regular join, we will not be able to get what we want because we will have missed "New York," since it does not appear in the Store_Information table. Therefore, we need to perform an outer join on the two tables above:
SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
Note that in this case, we are using the Oracle syntax for outer join.
Result:
store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250
Note: NULL is returned when there is no match on the second table. In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL.
SELECT A1.store_name, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name (+)
GROUP BY A1.store_name
Note that in this case, we are using the Oracle syntax for outer join.
Result:
store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250
Note: NULL is returned when there is no match on the second table. In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL.