Posted by bbla32 on 02/19/07 23:26

> The best way to make the subqueries faster is to run them less. If
> the first WHEN is satisfied the subqueries are not run at all. If the
> first subquery matches, the second subquery is not run at all. In the
> original query every subquery was run against every row in the table -
> and every row was processed as many times as there were UNIONed
> SELECTs. So the idea behind moving them into the CASE is to run them
> once per incoming row, and then as few of them as is required.

That makes sense. I have a problem with your query though: in the
construct like below

SELECT CASE
WHEN DM.ReceiverDepartmentID NOT IN (1)
THEN 0
ELSE 1
END

when DM.ReceiverDepartmentID is null, it returns 1 instead of 0?

[Back to original message]