"Not in" operator issue - SQL Server Forums

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)

"Not in" operator issue

Author

Topic

mandelbort
Starting Member

8 Posts

Posted - 2008-01-16 : 10:42:05

i'm trying to write a quite simple sql query in sql server:

select * from mytable
where (field1, field2) not in
(select fieldA, fieldB from mytable2)

but i get the error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

this query works fine in postgresql.
Any ideas?
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-16 : 10:45:41

You can only use that for a single column.

Change it to a left join.

CODO ERGO SUM

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-16 : 10:47:40

works in oracle as well, wont work in sql server

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-16 : 10:52:43

select t1.* from mytable as t1
where not exists (select * from mytable2 as t2 where t2.fieldA = t1.field1 AND t2.fieldB = t1.field2)

select t1.* from mytable as t1
left join mytable2 as t2 on t2.fieldA = t1.field1 AND t2.fieldB = t1.field2
where t2.fieldA is null

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-16 : 10:53:20

use exists:
http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

mandelbort
Starting Member

8 Posts

Posted - 2008-01-16 : 10:53:45

quote:
Originally posted by Michael Valentine Jones

You can only use that for a single column.

Change it to a left join.

CODO ERGO SUM

Can you indicate to me how to change to a left join? thanks!

mandelbort
Starting Member

8 Posts

Posted - 2008-01-16 : 10:55:14

thanks!! :)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-16 : 22:58:35

select * from mytable
where (field1+"/"+field2) not in
(select fieldA+"/"+fieldB from mytable2)

It will not be able to use any indexes though so Peso's might be better. There are differences in behaviour of EXISTS and IN where NULLs are present though so you should evaluate your options based upon the data & table definitions you have.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources