Delete Orphaned Child Records with a Subquery

DELETE FROM
    child_table
WHERE
    parent_id NOT IN (SELECT id FROM parent_table WHERE id IS NOT NULL);

-- Alternative using LEFT JOIN (often more performant for large tables)
-- PostgreSQL:
-- DELETE FROM child_table
-- USING parent_table
-- WHERE child_table.parent_id = parent_table.id AND parent_table.id IS NULL;

-- MySQL:
-- DELETE child_table
-- FROM child_table
-- LEFT JOIN parent_table ON child_table.parent_id = parent_table.id
-- WHERE parent_table.id IS NULL;

How it works: This query identifies and deletes "orphaned" child records. An orphaned record is one that references a `parent_id` that does not exist in the `parent_table`. The primary approach uses a subquery with `NOT IN` to find such records, ensuring that `NULL` values in the subquery are handled if possible. The commented alternative, often more performant for very large tables, uses a `LEFT JOIN` to identify child records where no matching parent exists (`parent_table.id IS NULL`) and then deletes them. The `DELETE` syntax varies slightly between PostgreSQL and MySQL for join-based deletes.