My Blog List

Monday, March 18, 2013

Anti join in mysql

create table tab_tset1 (id INT, PRIMARY KEY(id));
create table tab_test2(id INT);
insert into tab_test1 values (1), (2), (3), (4);
insert into tab_test2 values (1), (2);

select t1.id from tab_test1 t1
where t1.id not in (select t2.id from tab_test2 t2);

select t1.id from tab_Test1 t1
where not exists
(select 1 from tab_tetst2 t2 where t2.id=t1.id);

select t1.id
from tab_test1 t1
left outer join tab_test2 t2 on t1.id=t2.id
where t2.id is null;


choose one thing that you want

if there are a lot of datas i prefer to use third way

No comments:

Post a Comment