|
In an left outer join, all rows from the first table mentioned in the SQL query is selected, regardless whether there is a matching row on the second table mentioned in the SQL query. Let's assume that we have the following two tables,
Table Store_Information
| store_name | Sales | 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.
Next: SQL Cross Join
|
| Copyright © 2013 1keydata.com All Rights Reserved.
Privacy Policy |
|