Another question (IN keyword)

Is possible in SQLSERVER to use a syntax like this?

select x,y from table_1 where (x,y) not in (select h,k from table_2)

I've tried, but it doen't work.

Do you know any workaround?

Thank you

Fede"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:cjc1bn$nsk$1@.atlantis.cu.mi.it...
> Is possible in SQLSERVER to use a syntax like this?
> select x,y from table_1 where (x,y) not in (select h,k from table_2)
> I've tried, but it doen't work.
> Do you know any workaround?
> Thank you
> Fede

No, that's not supported in MSSQL - you can use a correlated subquery
instead:

select x, y
from table_1 t1
where not exists (
select *
from table_2 t2
where t1.x = t2.h
and t1.y = t2.k)

Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
migration guide (they do exist for other database platforms), but you might
still find some useful stuff here:

http://www.microsoft.com/sql/evalua...ibm/default.asp

Simon|||> No, that's not supported in MSSQL - you can use a correlated subquery
> instead:
> select x, y
> from table_1 t1
> where not exists (
> select *
> from table_2 t2
> where t1.x = t2.h
> and t1.y = t2.k)
> Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
> migration guide (they do exist for other database platforms), but you
might
> still find some useful stuff here:
> http://www.microsoft.com/sql/evalua...ibm/default.asp
> Simon

Thank you very much!

Fede|||"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message news:<cjdp11$nm6$1@.atlantis.cu.mi.it>...
> > No, that's not supported in MSSQL - you can use a correlated subquery
> > instead:
> > select x, y
> > from table_1 t1
> > where not exists (
> > select *
> > from table_2 t2
> > where t1.x = t2.h
> > and t1.y = t2.k)
> > Unfortunately, Microsoft doesn't seem to have a DB2 to MSSQL technical
> > migration guide (they do exist for other database platforms), but you
> might
> > still find some useful stuff here:
> > http://www.microsoft.com/sql/evalua...ibm/default.asp
> > Simon
> Thank you very much!
> Fede

Or, somewhat equivalently, you could do:

SELECT t1.x,t1.y FROM Table_1 t1 LEFT JOIN Table_2 t2 ON t1.x = t2.h
and t1.y = t2.k WHERE t2.h IS null

Sorry, just have an intense dislike of "not in" and "not exists". This
second form may (or may not, YMMV) perform better