Question

You have a database with two tables: PARENT and CHILD. CHILD has a column PARENT_ID that references the ID column in the PARENT table. Write an SQL statement to find and delete orphaned records in the CHILD table.

Answer 1

Note: Done accordingly. Please comment for any problem. Please Uprate. Thanks.

Query:


DELETE c 
FROM CHILD c  
LEFT JOIN PARENT p 
ON c.PARENT_ID = p.ID 
WHERE p.ID IS NULL 

Here what LEFT JOIN do ?

When we execute a query using the LEFT JOIN syntax, then it returns all of the records from both tables that contain matching values, as defined by the ON clause. It also returns all of the records from the table on the left side even if there aren’t any matching values in the table on the right.

So here we will get all values of child table + values where p.ID is null and then we are deleting all those rows.