SQL Tutorial


SQL SELECT
SQL DISTINCT
SQL WHERE
SQL AND OR
SQL IN
SQL BETWEEN
SQL LIKE
SQL ORDER BY
SQL Functions
SQL COUNT
SQL GROUP BY
SQL HAVING
SQL ALIAS
SQL JOIN
SQL OUTER JOIN
SQL Subquery
SQL UNION
SQL UNION ALL
SQL INTERSECT
SQL MINUS














Data Warehousing & Business Intelligence
Area Codes Info
State Info
HTML Tutorial


























Home
SQL Commands
Table Manipulation
SQL Syntax

SQL Outer Join

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 JOIN command.

The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE cluase 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 Georgraphy 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_nameSALES
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.

Next Section

Site Map   |   Resources

1