Presents your  
SQL SERVER E-NEWSLETTER for January 6, 2004 
 
<-------------------------------------------> 
  
USE AN OUTER JOIN TO IDENTIFY POOR SELLING PRODUCTS   
  
The 80/20 rule has numerous applications, one of which says that 20 
percent of the products you sell make up 80 percent of your sales.
Therefore, it's in your company's interest to identify the worst-performing 
products.   
  
The following query uses the TOP keyword and a computed column to 
determine the 10 products that have generated the least revenue. It 
uses the Northwind sample database.   
  
SELECT TOP 10 [Order Details].ProductID, ProductName, SUM(Quantity * 
[Order Details].UnitPrice) AS Amount   
FROM [Order Details] INNER JOIN Products ON [Order Details].ProductID =  
 Products.ProductID   
GROUP By [Order Details].ProductID, ProductName   
ORDER BY Amount   
  
This yields the 10 products that generate the least revenue in 
ascending order by revenue, but there's a problem. To see the problem, add a
new row to the Products table (which won't have any sales yet). Rerun the 
query above; this new product doesn't appear in the list even though it 
should. We have to rewrite the query using a LEFT OUTER JOIN.   
  
SELECT TOP 10 Products.ProductID, Products.ProductName, SUM([Order   
Details].Quantity * [Order Details].UnitPrice) AS Amount   
FROM  Products LEFT OUTER JOIN   
[Order Details] ON Products.ProductID = [Order Details].ProductID   
GROUP BY Products.ProductID, Products.ProductName   
ORDER BY Amount   
  
The new product tops the list, with its value of the computed Amount 
column set to NULL. If you prefer to have a zero-dollars value there, 
wrap the Amount expression with the ISNULL() function, like this:   
  
SELECT TOP 10 Products.ProductID, Products.ProductName,   
ISNULL(SUM([Order Details].Quantity * [Order Details].UnitPrice),0) AS 
Amount   
FROM Products LEFT OUTER JOIN   
[Order Details] ON Products.ProductID = [Order Details].ProductID   
GROUP BY Products.ProductID, Products.ProductName   
ORDER BY Amount   
  
Now our list is nicely formatted, with a zero value in its top row.   
  
It's easy to overlook the need for an OUTER JOIN in queries such as 
this, especially when the existing data hides the exceptional case.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
  
----------------------------------------