I spent most of the day trying to figure out one simple outer join query in SQLServer 2005, it turns out that the problem stems from the WHERE clause, apparently SQL Server will execute the WHERE clause before the join? Which doesn't make much sense to me, it seems like a bug, but whatever, at least I only wasted about six hours trying to figure out that once you put the criteria within the join clause it works as expected.
Here are some more articles I found on the subject:
Outer Join trouble
Additional Criteria in the JOIN Clause
Friday, June 15, 2007
Subscribe to:
Post Comments (Atom)
2 comments:
I got to this bug in SQL 2008 as well :( I'm trying to get a NULL or a zero for certain value in the RIGHT table as there is no data for it the LEFT table and with a where clause, SQL simply suppresses the RIGTH table value...
It's not a bug. If you want to restrict the results on the joined table, add [and column = value] to the join clause.
Post a Comment