SELECT * FROM TABLEA A WHERE NOT EXISTS (SELECT 0 FROM TABLEB B WHERE B.ID=A.ID)
Create table Table_A ( USER_ID CHAR(10), USER_Name varchar2(16), User_Birthday date );Create table Table_B ( USER_ID CHAR(10), USER_Name varchar2(16), User_Birthday date );insert INTO Table_A ( SELECT '101','张三',to_date('1977-02-12','yyyy-MM-dd') from dual union SELECT '102','李四',to_date('1976-12-22','yyyy-MM-dd') from dual union SELECT '103','王五',to_date('1975-10-11','yyyy-MM-dd') from dual);insert INTO Table_B ( SELECT '101','张三',to_date('1977-02-12','yyyy-MM-dd') from dual union SELECT '102','李四',to_date('1976-12-22','yyyy-MM-dd') from dual); select * from table_b minus select * from table_a;
select * from table_b a where not exists ( select 0 from table_a b where a.user_id=b.user_id);
按楼上的数据资料,应该这样才能有结果:
SELECT * FROM TABLE_A MINUS SELECT * FROM TABLE_B; SELECT * FROM TABLE_A A WHERE NOT EXISTS (SELECT * FROM TABLE_B B WHERE B.USER_ID=A.USER_ID);
to wsk_228: 多谢指正.to zkhj 可以参考下面语句: merge into table_a a using (select user_id, user_name, user_birthday from table_b ) b on(a.user_id=b.user_id) when matched then update set a.user_name=b.user_name, a.user_birthday=b.user_birthday when not matched then insert (user_id, user_name, user_birthday) values (b.user_id, b.user_name, b.user_birthday)
WHERE NOT EXISTS (SELECT 0 FROM TABLEB B WHERE B.ID=A.ID)
(
USER_ID CHAR(10),
USER_Name varchar2(16),
User_Birthday date
);Create table Table_B
(
USER_ID CHAR(10),
USER_Name varchar2(16),
User_Birthday date
);insert INTO Table_A (
SELECT '101','张三',to_date('1977-02-12','yyyy-MM-dd') from dual
union
SELECT '102','李四',to_date('1976-12-22','yyyy-MM-dd') from dual
union
SELECT '103','王五',to_date('1975-10-11','yyyy-MM-dd') from dual);insert INTO Table_B (
SELECT '101','张三',to_date('1977-02-12','yyyy-MM-dd') from dual
union
SELECT '102','李四',to_date('1976-12-22','yyyy-MM-dd') from dual);
select * from table_b minus select * from table_a;
select * from table_b a
where not exists ( select 0 from table_a b where a.user_id=b.user_id);
SELECT * FROM TABLE_A MINUS SELECT * FROM TABLE_B; SELECT * FROM TABLE_A A
WHERE NOT EXISTS (SELECT * FROM TABLE_B B WHERE B.USER_ID=A.USER_ID);
可以参考下面语句:
merge into table_a a
using (select user_id, user_name, user_birthday
from table_b ) b
on(a.user_id=b.user_id)
when matched then
update
set a.user_name=b.user_name, a.user_birthday=b.user_birthday
when not matched then
insert (user_id, user_name, user_birthday)
values (b.user_id, b.user_name, b.user_birthday)