关于 sql 查询和删除....请教！

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 3016 days ago, the information mentioned may be changed or developed.

select A.ID from A left join B on A.ID=B.ID where B.ID is null

1.我用以上语句搜出了和 B 表未关联的 A.ID

2.请问我该如何写 sql 才能直接删除这些 A.ID ，既一条语句删除查出来未关联的 ID

SQL

删除

语句

关联

8 replies • 2018-07-20 22:43:44 +08:00

cnmllll

Jul 20, 2018

delete from A.ID where A.ID not not in (select A.ID from A left join B on A.ID=B.ID where B.ID is null)
这样可以么

cnmllll

Jul 20, 2018

delete from A where A.ID not not in (select A.ID from A left join B on A.ID=B.ID where B.ID is null)
这个

jintianfengda

Jul 20, 2018

DELETE
a
FROM
a
LEFT JOIN
b
ON
a.ID = b.ID
WHERE
b.id is null

cnmllll

Jul 20, 2018

delete from A where A.ID in (select A.ID from A left join B on A.ID=B.ID where B.ID is null) 这个终极版

akira

Jul 20, 2018

这个 sql 和 select id from A where id is null 有啥区别

lrh3321

Jul 20, 2018

@akira 表 A 中的行在表 B 中不一定有对应的行，他要删的就是这些无用的行。

akira

Jul 20, 2018

@lrh3321 嗯呐，审题不仔细 >"<

iwiki

Jul 20, 2018

delete from a where not exists (select 1 from b where a.id=b.id)