Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

NOT IN alternative in 2008

is there any new efficient syntax to rewrite
SELECT  * from table1 where WHERE NNS NOT IN  (SELECT NNS FROM table2)
in 2008?
0
25112
Asked:
25112
  • 2
2 Solutions
 
Jared_SCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
25112Author Commented:
thanks
0
 
Jared_SCommented:
Also, be aware that except and intersect will compare EACH field in the row, not just the ID.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now