Left Outer Join in SQL - sql - sql tutorial - learn sql

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.
This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

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.

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

SQL Joins

Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

All SQL Joins

Related Searches to Left Outer Join in SQL

mysql left joinleft inner joinleft join vs left outer joininner join vs left joinleft outer join vs left joinsql left outer join exampleleft outer join sql serverleft outerleft outer join sql exampleleft outer join sql server exampleleft outer join in pl sqlsql left outer join selectsql select left outer joinleft outer join select statementright outer join in sqlleft outer join oracleleft outer join vs left joinleft outer join mysqlleft join oracleleft inner joinleft join multiple tablessql left inner join