>>Most times when people use IN, they use it with a subquery that isn't written as a correlated subquery.
>>
>>Again, what you have works. (Admittedly, when I read it, I wondered if it would generate an error, because I assumed...dangerous world....that IN would not work with subqueries that referenced a table from the outer query).
>>
>>If you were worried about NULL values in the Province table, then EXISTS might be better (EXISTS certainly works better if you have to search on multiple columns)
>>
>>Maybe Naomi or Sergey or someone else who has run into this can jump in - admittedly this was the first time I'd ever seen an IN with a subquery written as a correlated subquery. It seemed a bit odd and I was actually surprised it worked. But maybe I'm missing something.
>
>Exists might have been a better choice actually. Thanks

What I learned is that IN with a subquery is always very poor performance and should never be done. Always use Exists instead.

http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance