Previously, we had looked at
left join, or inner join, where we
select rows common to the
participating tables to a join.
What about the cases where we are
interested in selecting elements
in a table regardless of whether
they are present in the second
table? We will now need to use the SQL
OUTER JOINcommand.
The syntax for performing an
outer join in SQL is
database-dependent. For example,
in Oracle, we will place an "(+)"
in the WHERE clause
on the other side of the table for
which we want to include all the
rows.
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.
