sql查询一个表里的数据在另一个表是否存在

Question

sql查询一个表里的数据在另一个表是否存在,如果不存在就写入这个表，存在就不写，用SQL完成怎么写

Answer 1

insert into tableA
select * from tableB b where not exists(select 1 from tableA a where a.id = b.id)
insert into tableA
select * from tableB b
left join tableA a on a.id = b.id
where a.id is null
----用 not exists来判断,left join ，其中条件为唯一性主键

Answer 2

--创建测试表
CREATE TABLE AA_TMP(ID VARCHAR2(10)); --插入目标表
CREATE TABLE AA_LOOKUP(ID VARCHAR2(10));--要插入的数据

INSERT INTO AA_LOOKUP VALUES('1');
INSERT INTO AA_LOOKUP VALUES('2');
INSERT INTO AA_LOOKUP VALUES('3');
COMMIT;

--插入数据不存在时更新
INSERT INTO AA_TMP(ID)
SELECT ID FROM AA_LOOKUP A
WHERE NOT EXISTS
(SELECT 1 FROM AA_TMP B
WHERE A.ID=B.ID
)
;
COMMIT;

Answer 3

1先判断是否存在
if exists(select * from 表名where 字段not in '数据')
insert添加

Answer 4

select * from swdj where qymc not in (select qymc from gsdj)