Solved

SQL Select Question

Posted on 2016-09-09
15
65 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
ID: 41791626
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
ID: 41791632
@Jim Horn

This is a test question for 70-461.
0
 

Author Comment

by:isames
ID: 41791634
I'm thinking it's because "Not In" doesn't know how to handle Nulls, but I'm not sure
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 41791641
>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
ID: 41791695
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
ID: 41791739
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
ID: 41791783
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41793833
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
ID: 41794124
@Vitor Montalvao

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

Expert Comment

by:Vitor Montalvão
ID: 41794129
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
ID: 41794152
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 47

Expert Comment

by:Vitor Montalvão
ID: 41794167
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
ID: 41794193
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
ID: 41799874
Thanks everyone
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41799892
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 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