Question

写SQL语句，删除A表记录在B表不存在的数据

1、有T_AD, T_AD_PLAY 两张表，表中字段如下：

T_AD: N_CODE,S_NAME ,…

T_AD_PLAY:N_CODE,N_AD,T_TIME1,…

现已知
(1) T_AD_PLAY 表中的N_AD 是T_AD表的外键,两个表的N_CODE都是主键
(2) 两张表的表数据量都大于100万行
请写出如下SQL：删除T_AD表记录在T_AD_PLAY不存在的数据。.
这是一家公司的笔试题

Answer 1

1. 采用not exists 执行速度快
delete from T_AD where not exists (select 1 from T_AD_PLAY where T_AD.N_CODE =T_AD_PLAY.N_CODE )
2. 采用not in 执行速度慢
Delete From T_AD Where N_AD Not In(Select N_AD From T_AD_PLAY)

Answer 2

T_AD表中的哪个字段的记录在T_AD_PLAY表中的哪个字段的记录中不存在？
假设是删除T_AD表中N_CODE在T_AD_PLAY表中的N_CODE不存在的记录吧
delete from T_AD where T_AD.N_CODE not in (select T_AD_PLAY.N_CODE from T_AD_PLAY)

Answer 3

delete from T_AD
where T_AD.N_CODE
not in (select T_AD.N_CODE from T_AD
where T_AD.N_CODE =T_AD_PLAY.N_CODE )

Answer 4

delete from T_AD
where not exists (select 1 from T_AD_PLAY where T_AD.N_CODE =T_AD_PLAY.N_CODE )

这才是快的

Answer 5

delete from T_AD where T_AD.N_CODE NOT EXISTS (SELECT N_AD FROM T_AD_PLAY)