Solved

NOT IN alternative in 2008

Posted on 2013-01-31
4
128 Views
Last Modified: 2013-01-31
is there any new efficient syntax to rewrite
SELECT  * from table1 where WHERE NNS NOT IN  (SELECT NNS FROM table2)
in 2008?
0
Comment
Question by:25112
[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
  • 2
4 Comments
 
LVL 12

Assisted Solution

by:Jared_S
Jared_S earned 250 total points
ID: 38840370
select * from table1 where not exists
(select null from table2 where table2.nns = table1.nns)

will work in any release and is much much more efficient than NOT IN.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 250 total points
ID: 38840730
You can also look at new(er) EXCEPT (and INTERSECT) commands so a sample code would be like:

select * from table1
except
select * from table2 where table2.nns = table1.nns

if you have TEXT fileds you need to CAST(col_name AS VARCHAR(MAX)) for the EXCEPT to work.
0
 
LVL 5

Author Comment

by:25112
ID: 38840845
thanks
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38840891
Also, be aware that except and intersect will compare EACH field in the row, not just the ID.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 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