Solved

SQL Select Question

Posted on 2016-09-09
15
50 Views
Last Modified: 2016-09-20
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
Comment
Question by:isames
  • 4
  • 4
  • 3
  • +2
15 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
 

Author Comment

by:isames
Comment Utility
@Jim Horn

This is a test question for 70-461.
0
 

Author Comment

by:isames
Comment Utility
I'm thinking it's because "Not In" doesn't know how to handle Nulls, but I'm not sure
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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
 
LVL 15

Expert Comment

by:gplana
Comment Utility
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
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
This can only be an exam question because in the real word people wouldn't use any of the options.
1
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
@Vitor Montalvao

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

Expert Comment

by:Vitor Montalvão
Comment Utility
A LEFT JOIN returning only the NULL values or a NOT EXIST clause. And these are ANSI solutions.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
 

Author Comment

by:isames
Comment Utility
Thanks everyone
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now