How to use NOT IN clause in sqlserver? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use NOT IN clause in sqlserver?

Author

Topic

govindts
Starting Member

33 Posts

Posted - 2008-03-07 : 23:22:49

All, I need to help on the below query.

SELECT * FROM TAB1 WHERE (COLUMN1, COLUMN2) NOT IN(SELECT
COLUMN1, COLUMN2 FROM TAB2)

The above query is not working in sqlserver. Please let me know how i can achieve this in sqlserver?

Thanks in advance.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-07 : 23:34:08

Tried this?

SELECT * FROM TAB1 WHERE (COLUMN1 NOT IN(SELECT COLUMN1 FROM TAB2)) and (COLUMN2 NOT IN(SELECT COLUMN2 FROM TAB2))

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-07 : 23:35:28

Hi

try with this

select * From Tab1
where col1 not in (Select col1 From Tab2)
and col2 not in (Select Col2 from Tab2)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-08 : 10:33:51

Both not right.

SELECT *
FROM Table1 AS t1
WHERE NOT EXISTS (SELECT * FROM Table2 AS t2 WHERE t2.Column1 = t1.Column1 AND t2.Column2 = t1.Column2)

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-08 : 11:22:41

Can you do this also?


Select t1.*,t2.Column1,t2.column2
FROM Table t1 LEFT JOIN Table2 t2 
     on t1.Column1 = t2.Column1  AND
        t1.Column2 = t2.column2
WHERE t2.column1 is null

I presume NOT EXISTS is a better method ?

Poor planning on your part does not constitute an emergency on my part.

govindts
Starting Member

33 Posts

Posted - 2008-03-08 : 15:21:23

All, Thank you all for replying my question. Peso Answer worked very well and it solved my problem. Again, i thank and appreciate every one in this thread. This forum is very usefull.

SELECT *
FROM Table1 AS t1
WHERE NOT EXISTS (SELECT * FROM Table2 AS t2 WHERE t2.Column1 = t1.Column1 AND t2.Column2 = t1.Column2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources