Solved

NOT IN alternative in 2008

Posted on 2013-01-31
4
125 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
  • 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 39

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
tempdb log contention 16 49
SQL Server 2012 r2 Make faster Temp Table 17 113
MS SQL BCP Extra Lines Between Records 2 23
Sql query with where clause 2 33
In this article I will describe the Backup & Restore 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.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 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