SQL LEFT OUTER JOIN

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_Name	Sales	Txn_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

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.