SQL > SQL JOIN > Left Outer Join

In an left outer join, all rows from the first table mentioned in the SQL query is selected, regardless whether there is a matching row on the second table mentioned in the SQL query. Let's assume that we have the following two tables,

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

Table Geography

Region_NameStore_Name
EastBoston
EastNew York
WestLos Angeles
WestSan Diego

We want to find out sales by store, and we want to see the results for all stores regardless whether there is a sale in the Store_Information table. To do this, we can use the following SQL statement using LEFT OUTER JOIN:

SELECT A1.Store_Name STORE, SUM(A2.Sales) SALES
FROM Geography A1
LEFT OUTER JOIN Store_Information A2
ON A1.Store_Name = A2.Store_Name
GROUP BY A1.Store_Name;

Result:

STORE SALES
Los Angeles 1800
San Diego 250
New York NULL
Boston 700

By using LEFT OUTER JOIN, all four rows in the Geography table is listed. Since there is no match for "New York" in the Store_Information table, the Sales total for "New York" is NULL. Note that it is NULL and not 0, as NULL indicates there is no match.

Next: SQL Cross Join




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy