2个表找出多余的用户名

[yingzi0011]

2个表，

user 表，存储用户注册信息。字段 username

main 表，存储产品信息 ，username 用来记录是谁发布的

以上是前提：

最近很多人来注册，但是注册完不发产品信息。
长时间下来，在user表造成大量的垃圾注册信息（没有发布信息的）

有语句可以删除吗？

通过检测 main 表的 username 和 user表的 username

1 列出 user表中的没有发布产品的用户
2 删除 user表中没有发布产品的用户

[xuam]

1.select * from user where username not in (select username from main)
2.delte from user where username not in (select username from main)

[xuam]

2.delete from user where username not in (select username from main)

[Quote=引用 1 楼 xuam 的回复:]
1.select * from user where username not in (select username from main)
2.delte from user where username not in (select username from main)
[/Quote]

[快溜]

delete user 
where username in (select username from main where 产品信息 is null)

[yy1987316]

select * from user where username not in (select distinct username from main)--1列出 user表中的没有发布产品的用户
delete from user where username not in (select distinct username from main)--2 删除 user表中没有发布产品的用户

[飘零一叶]

--查找
select a.*,b.* 
from user a left join main b on a.username=b.username
where b.username is null
--删除
delete a
from user a left join main b on a.username=b.username
where b.username is null

[DataBox-MDX]

--1
select username from user t1 where not exists(select top 1 * from main t2 where t1.username=t2.username)
--2
delete from user t1 where not exists(select top 1 * from main t2 where t1.username=t2.username)

[gw6328]

delete [user] where username not in (select [username] from main group by username)