Solved

NOT IN alternative in 2008

Posted on 2013-01-31
4
126 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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