//---------------------- 建表1 ----------------------
create table TESTTABLE
(
id1 VARCHAR2(12),
name VARCHAR2(32)
)
select t.id1,t.name from TESTTABLE t
insert into TESTTABLE (ID1, NAME)
values ('1', 'zhangsan');
insert into TESTTABLE (ID1, NAME)
values ('2', 'lisi');
//---------------------- 建表2 ----------------------
create table test_user
(
id VARCHAR2(12),
name VARCHAR2(32),
age number(2)
)
select t.id,t.name,t.age from test_user t
insert into test_user (ID, NAME,age)
values ('1', '张三',12);
insert into test_user (ID, NAME,age)
values ('2', '李四',12);
insert into test_user (ID, NAME,age)
values ('3', '王五',12);
使用not in的sql:
select ID,name from test_user where ID not in (select ID1 from TESTTABLE)
使用exit的sql:
SELECT T.id,T.name FROM test_user T WHERE NOT EXISTS ( SELECT 'G' FROM TESTTABLE TT WHERE TT.id1 = T.id )
使用left join的sql:
select test_user.ID,test_user.name,TESTTABLE.ID1,TESTTABLE.NAME from test_user left join TESTTABLE on test_user.id=TESTTABLE.ID1 where TESTTABLE.name is null