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.
 representation of join types
  • 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

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

All SQL Joins

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

Related Searches to Left Outer Join in SQL