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
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