How can we trust Outer Joins

In the future =* or *= will not be suportted but I found more then one examples that show outer join does not bring all the rows =* does.

Can any one tell me what is wrong? Market table has 19 markets Budget table has rows for BudgetYear=2006 but none for 2007

BTW the same is true in SQL2005 and 2000

-

SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_code
Where Budget.BudgetYear=2007 or Budget.BudgetYear is null
GROUP BY Market_code, Description
ORDER BY Description

-- GIVES only 12 results -

SELECT Market_code, Description,
Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),
CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),
Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )
FROM Budget, Market
Where Budget.Customer_Market_code =* Market.Market_code
AND Budget.BudgetYear=2007
GROUP BY Market_code, Description
ORDER BY Description

-- GIVES 19 results -the true

? You're falling into what some call the "trap" of the outer join... Putting predicates in the WHERE clause vs. the JOIN clause has a different logical meaning when working with outer joins. Keep in mind that the join expression is logically evaluated before the WHERE clause. With that in mind, it's a simple move: SELECT Market_code, Description,Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_code AND Budget.BudgetYear=2007GROUP BY Market_code, DescriptionORDER BY Description -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Ofer Gal@.discussions.microsoft.com> wrote in message news:641c616b-1b8a-4316-9278-f52db877ae74@.discussions.microsoft.com... In the future =* or *= will not be suportted but I found more then one examples that show outer join does not bring all the rows =* does. Can any one tell me what is wrong? Market table has 19 markets Budget table has rows for BudgetYear=2006 but none for 2007 BTW the same is true in SQL2005 and 2000 - SELECT Market_code, Description,Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )FROM Budget RIGHT OUTER JOIN Market ON Budget.Customer_Market_code = Market.Market_codeWhere Budget.BudgetYear=2007 or Budget.BudgetYear is nullGROUP BY Market_code, DescriptionORDER BY Description -- GIVES only 12 results - SELECT Market_code, Description,Revenue = SUM( CASE WHEN BudgetType = 'B' THEN BudgetAmount ELSE 0 END ),CM = SUM( CASE WHEN BudgetType = 'C' THEN BudgetAmount ELSE 0 END ),Tooling = SUM( CASE WHEN BudgetType = 'T' THEN BudgetAmount ELSE 0 END )FROM Budget, Market Where Budget.Customer_Market_code =* Market.Market_codeAND Budget.BudgetYear=2007GROUP BY Market_code, DescriptionORDER BY Description -- GIVES 19 results -the true|||when ther outer table does not meet the requirements of the JOIN clause, the values of all its' columns is NULL, thus, any further filtering (other then null-check) on those will cause the row to be removed from the result.