Multi column subqueries in DB2?

nottoknow · #1 (**permalink**) 11-27-08, 12:15

Is there a way to write multi-column subqueries in DB2? I tried following

select * from table1 where (field1,field2) not in (select field1,field2 from table2 )

thanks

arlf · #2 (**permalink**) 11-27-08, 12:39

Try

select * from table1 a
where not exists ( Select 1 from table2 b
where b.field1 = a.field1
and b.field2 = a.field2 )

Saludos, ARLF.

stolze · #3 (**permalink**) 11-27-08, 12:59

Both queries are perfectly fine. What's the problem you have with the first, non-correlated subquery?

Code:

$ db2 "select * from t where (a, b) not in ( select c1, c2 from x )"

A           B
----------- -----------

  0 record(s) selected.

tonkuma · #4 (**permalink**) 11-27-08, 13:14

Quote:

select * from table1 where (field1,field2) not in (select field1,field2 from table2 )

You can do that in DB2 for LUW. DB2 for z/OS also support that syntax.

For more general syntax, you can see in
"DB2 SQL Reference" ---> "Chapter 2. Language elements" ---> "Predicates" ---> "IN predicate" and "Quantified predicate".

You can see DB2 9 for LUW PDF manuals here.
http://www-01.ibm.com/support/docvie...id=swg27009552
or
http://www-01.ibm.com/support/docvie...id=swg27009552
DB2 9 for Linux, UNIX and Windows manuals

Manual

Abstract
This page contains English Portable Document Format (PDF) manuals for DB2® 9 for Linux®, UNIX® and Windows® products, and additional components such as DB2 Connect™ and WebSphere® Federation Server.

Internet Services

Web Development

Digital Marketing

Consumer Tech