Jamie-2229 (12/25/2014)

---

I'm coming in late on this question. It's been my experience the "IN" operator in SQL is EXTREMELY inefficient. The why it is inefficient - the "why" is key here. SQL Server uses a query execution plan based on data it can expect to see in a query. An IN statement is ambiguous. Anything can be "IN". As our example shows, this includes NULL which will compare with everything thus you get the dumb answer of nothing even though we know there are several items not in the Table2 name.

To iterate on why (IMHO) SQL should eliminate the use of the IN statement, take an example from DEVELOPER SA (short from smart something or other) who runs a statement to lookup several thousand (or more) users and proceeds to put their ID numbers (an index of names for example) into a comma delimited list and then use the IN keyword to create the query.

SQL hasn't a clue what to expect so the query plan goes out the window. Meanwhile, had DEVELOPER SA just joined his first query to the source, a simple WHERE statement or a condition for the ON would have given him a super fast query.

Meanwhile, the query DEVELOPER SA has provides leaves poor SQL Server chugging and chugging and chugging while another 10 thousand users or so are wondering why the threading is so poor and imagining there is something wrong with their network.

OK, so a bit dramatic, yes. But I find the IN statement evil! Ok for a couple of items (if you remember to remove the NULL of course), but disaster for a large list.

OH YEAH - one more thing. The last answer was more correct. The user should have seen the NULL and fixed the query. Zero is not a character, it is a number. Maybe the answer meant rows? No brainer, there should have been rows here and there wasn't - FIX IT!