How to delete data from the clans table, provided that the necessary data is missing in the clan_players table?

Question

MySQL

Hello.
There is a clans table, it has a leader field.
There is a clan_players table, it has a name field.
It is necessary to delete the data in the clans table by the leader field.
Bottom line: If there is no data in the name field in the clan_players table, then it is necessary that the data in the clans table be deleted in the leader field.
leader and name is a nickname. In clans leader - IAlekseyI, and in clan_players the name field should be IAlekseyI. If not, delete data in clans (leader).
It is difficult to explain, but I hope someone understood me .. If anything, ask questions, what may need to be clarified ...

Answer 1

if I understand you correctly, then you need to delete all records in the clans table, for which there are no leaders in the players table,
for this it is logical to use the LEFT JOIN
list of leaders that do not have in the players list
check if everything is ok - then everything is simple

DELETE FROM clans WHERE leader in (SELECT c.leader FROM clans c LEFT JOIN clan_players cp ON c.leader = cp.name WHERE cp.name IS NULL);