Solved

SQL Select Question

Posted on 2016-09-09
15
72 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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 27

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 50

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 27

Expert Comment

by:Zberteoc
ID: 41794124
@Vitor Montalvao

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

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 27

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 50

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 27

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 50

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Lag Function DateDiff 2 26
Question about Common Table Expressions 3 45
Access PS SQLSERVER from powershell 1 30
SQL State HYT00. Timeout expired proplem 8 44
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

752 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