>>If, in practice, you can write one of them, then I don't think it matters (too much) - and actually AFAIK under the covers an IN query is treated as an (x = ... OR x = ...) query and is a shorthand notation, surprised that you are seeing different plans, maybe changed in recent versions. If you are looking it from ANSI SQL perspective, yes IN syntax is ANSI SQL, but beware that MS SQL server has its own extension to standard and thus NULL in the values wouldn't behave as it should with any ANSI SQL database. Based on settings, MS SQL would simply accept x=NULL comparison as if you did 'x is NULL' and that is not an ANSI SQL. Thus, (based on settings) IN (...., NULL) would be treated as x is = to one of the values or x is null.
>>
>>My preference is really neither, but an EXISTS query. Ibn practice I would think the values are coming from another table and thus EXISTS (or an inner join) query sounds to be better. If you are hardcoding the values then I think it doesn't matter which one you use (hardcoded values might make sense when they are enumeration values).
>
>When I applied a hardcoded syntax, it improved performance a lot. I had INNER JOIN before and that would overload the query performance degradation as well as diminishing the execution plan possibility to use a compound index from the master table with that additional field.
>
>If there is a table with 2 million records with a NoProvince key which is related to a Province table, that wouldn't change that much as a province table would be limited to about 12 records for Canada, or Provinces + States, under about a hundred. So, that would probably be cached and everything would go fast. But, if we use a NoSomething field into field that represents a relational key in a table which has 200000 records, then the INNER JOIN would drop performance a lot, as per what I mentioned in the first paragraph.
>
>Do you see EXISTS as potentially an alternative route here?


Yes, EXISTS is a potentially alternative route to take. Unlike inner join or IN ( select ... ) -which both need an aggregate phase-, EXISTS returns a boolean true on first hit and doesn't check the rest.

Also, I would have those few provinces in their own table and check from there instead of searching them from a 200K or 2M records (they should be using it as foreign keys).