If you want to (for example) delete all users with the guest role from your database :

DELETE FROM user WHERE EXISTS (
    SELECT * FROM role
    WHERE role.id = user.role_id
    AND role.name = 'guest'
);