Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sql of using exist on multi column

Status
Not open for further replies.

pengpeng

Programmer
Dec 18, 2002
6
US
I have two tables,

create table t1(
col11 int,
col12 int,
col13 int)

and

create table t2(
col21 int,
col22 int)

I tried to write a sql statement as follow to get what exist in t1 but not in t2 and vice versa.

>select * from t1
where (col11, col12) not exists (select col21, col22 from t2)

I got the error:
Incorrect syntax near ','.

Then I had to using,
>select *
from t1
where t1.col11 not in ( select t2.col21
from t2
where t1.col11=t2.col21
and t1.col12=t2.col22)
and t1.col12 not in ( select t2.col21
from t2
where t1.col11=t2.col21
and t1.col12=t2.col22)

It works. But it takes a long time to finishe. Is there a way to use exist on multiple columns in where clause, or an other way to write my sql to improve performance?

Thanks.
Pengpeng
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top