使用join删除多个表，而不是删除所有表

Question

我正在使用连接从多个表中删除，但是这个查询的问题是，如果一个表不包含匹配值/为空，则不执行具有匹配值和数据的表。我该如何解决这个问题?

CREATE DEFINER=`root`@`localhost` PROCEDURE `gdpr_delete`(_email_ varchar(128))
BEGIN
DELETE AppCoverLetter, AppError, AppFormData, AppJobData, AppTrackingData, FlowLog, App,AppResume
FROM AppCoverLetter t1
INNER JOIN (
SELECT AppId 
FROM ApplyData.AppFormData 
where lower(Email) = lower(_email_)
) t3 ON t1.AppID = t3.AppId
INNER JOIN AppError        ON AppError.AppID = t3.AppId
INNER JOIN AppCoverLetter  ON AppCoverLetter.AppID = t3.AppId
INNER JOIN AppFormData     ON AppFormData.AppID = t3.AppId
INNER JOIN AppJobData      ON AppJobData.AppID = t3.AppId
INNER JOIN AppTrackingData ON AppTrackingData.AppID = t3.AppId
INNER JOIN FlowLog         ON FlowLog.AppID = t3.AppId
INNER JOIN App             ON App.AppID = t3.AppId
INNER JOIN AppResume       ON AppResume.AppID = t3.AppId;
END

Answer 1

如果不总是匹配行，可以使用分隔删除和单连接来避免循环

DELETE  tx
FROM table3 tx
INNER JOIN (
SELECT AppId 
FROM ApplyData.AppFormData 
where lower(Email) = lower(_email_)
) t3 ON tx.AppID = t3.AppId

但是如果你有一些表具有稳固的持久关系那么对于这些表你可以使用单个查询进行多个删除而将单个连接删除只留给可选的关系