SQL > SQL JOIN > Left Outer Join

In an left outer join, all rows from the first table mentioned in the SQL query are selected, regardless whether there is at least one matching row on the second table 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 are 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.

Next: SQL Cross Join

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact