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

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf