有两张表T1,T2,表结构和数据如下:
想要实现T1中存在前提下,T2中不存在或者finished=0,也就是查询结果是:2, 4, 5, 6.
2. not exists:
create table t1 (
id int
);
create table t2 (
id int,
finished int
);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
insert into t1 values (4);
insert into t1 values (5);
insert into t1 values (6);
insert into t2 values (1,1);
insert into t2 values (2,0);
insert into t2 values (3,1);
insert into t2 values (4,0);
SQL Fiddle 可以测试SQL语句的执行。
想要实现T1中存在前提下,T2中不存在或者finished=0,也就是查询结果是:2, 4, 5, 6.
一、性能测试:
1. not in:explain (analyze,verbose,costs,buffers) select ID from T1 where ID not in (select ID from T2 where finished=1);
Total runtime: 0.128 ms
2. not exists: