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 tutorialSQL Joins
SQL Joins

Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialAll SQL Joins
All SQL Joins
