Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 86
  • Last Modified:

SQL Select Question

I need to retrieve a list of account holders who live in cities that don't have a branch location.


A.) Select AccountHolderID From AccountHolder Where CityID Not In (Select CityID From BranchMaster)

B.) Select AccountHolderID from AccountHolder Where CityID <> ANY (Select CityID from BranchMaster)


I chose A, but that is wrong. Can someone help me understand why.
0
isames
Asked:
isames
  • 4
  • 4
  • 3
  • +2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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?
0
 
isamesAuthor Commented:
@Jim Horn

This is a test question for 70-461.
0
 
isamesAuthor Commented:
I'm thinking it's because "Not In" doesn't know how to handle Nulls, but I'm not sure
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
gplanaCommented:
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.
0
 
ZberteocCommented:
The only reason I would see is if ANY was introduced with SQL 2012 because Microsoft documentation states clearly:

"The =ANY operator is equivalent to IN. For example, to find the names of all the wheel products that Adventure Works Cycles makes, you can use either IN or =ANY."

which means <>ANY is equivalent with NOT IN

SO the chose of B over A doesn't make to much sense.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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."
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This can only be an exam question because in the real word people wouldn't use any of the options.
1
 
ZberteocCommented:
@Vitor Montalvao

What would you, or real world people, use? :o)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
A LEFT JOIN returning only the NULL values or a NOT EXIST clause. And these are ANSI solutions.
0
 
ZberteocCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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"
0
 
ZberteocCommented:
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.
0
 
isamesAuthor Commented:
Thanks everyone
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
isames, if your question has been solved please close it by selecting the comment or comments that helped you sorting this out.
Cheers
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now