SQL Select Question

Hard to tell without looking at table schema and data, as I'd have gone with A as well, or better yet a JOIN..WHERE.

Where did you get this question from?   What version of SQL Server?

@Jim Horn

This is a test question for 70-461.

I'm thinking it's because "Not In" doesn't know how to handle Nulls, but I'm not sure

>This is a test question for 70-461.
Makes me wonder how I passed that exam three years ago.

Might be that both are correct, but the query engine stops processing once a match is found for the ANY, and processes the full set for the IN, so using ANY is more efficient.

Offhand, Microsoft exams used to be notorious for being heavily weighted toward new things introduced in that version, but the 2012 exams forward weren't too bad, so it wouldn't be surprising that questions will have SOME, ANY and that they are likely the correct answers.

The problem is that if you have a NULL value on  CityID then this SELECT doesn't work (test it), and I guess CityID could have nulls (doesn't seem to be the primary key, for example, and probably have not a NOT NULL restriction).

So another correct answer would be:

 Select AccountHolderID From AccountHolder Where CityID Not In (Select CityID From BranchMaster WHERE CityID IS NOT NULL)

But you definitely need thie WHERE clause to exclude null values. If not, then there is a NULL inside the IN operator so NOT IN will return always true.

Actually I respectfully disagree.  My Microsoft documentation states clearly:

"The =ANY operator was introduced as an ambiguous keyword that very few people will use so that we can ask real obscure questions on the 70-461 exam."

This can only be an exam question because in the real word people wouldn't use any of the options.

@Vitor Montalvao

What would you, or real world people, use? :o)

A LEFT JOIN returning only the NULL values or a NOT EXIST clause. And these are ANSI solutions.

It is the same issue, NOT IN is perfectly equivalent to using LEFT JOIN and you are free to choose any of them. Even the execution plans are identical. However I find the NOT IN solution way more suggestive, self explanatory, to what the actual intention is when it comes a scenario as described in this question. LEFT JOIN describes a more general relational status and is further away from the English language "likeness" in regards to this situation and we should not forget that the purpose of any programming language, and even more for SQL was exactly this, to be as close as possible to the natural language.

Even the execution plans are identical.

I'm not sure of that. How a JOIN between a PK and FK (used in both my suggestions) can have the same execution plan from a straight table scan (Select CityID From BranchMaster)?

to be as close as possible to the natural language.

That's why my choice goes to NOT EXISTS: "Give me everything from this table that NOT EXISTS in the other table"

For the first I suggest you to try. SQL engine is smart enough to know when to avoid table scans but make sure you are using PKs and indexed FKs like in a JOIN scenario.

For the second I agree, NOT EXISTS is another good choice that is closer to the logical intent.

Thanks everyone

isames, if your question has been solved please close it by selecting the comment or comments that helped you sorting this out.
Cheers