>>I normally use the second approach (e.g. IN) when the number of possibilities is limited (not a very long list)
>
>I would have thought the other one. As for me, this is what led me to use the IN approach, thus the number of possibilities.
>
>When I started to use the IN approach, it was for a specific query contained within an environment where the number of possibilities would be between 30 and 50. First of all, this makes the command quite long. So, the IN provides a cleaner and shorter command. But, it was only when there was so many choices. At first, under 5 or 6, I left the command as is. But, then, I adjusted it for the IN approach. Secondly, this is for performance. My query was based on a join table. So, this wasn't as fast as building the join values into a string and dumping that string inside the command itself. So, because the master table is big, this allowed to use faster queries as I eliminated a join clause. Then, today, I found the IN syntax is using a somewhat different execution plan. This is why I opened this discussion.

I read recently on MSDN about SQL 2016 problems with performance with very big IN lists, that's why I added not too big part. I think 50 items is still OK.