Jason-299789 (10/11/2012)
...as you're getting rid of a potentially expensive IN statement and using an Inner join from a CTE, the variable is no longer on the join and in the Where clause where it really belongs.
The IN construct in the OP's query is probably there to preserve the cardinality of the main part of the query with respect to CompanyID, which would make it functionally different to an IJ.
I'm curious if this is better or worse than the original, or matches ChrisM's.
The rewrite I posted isn't for performance - it's for accuracy.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]