SQL – Delete Orphans

In the 1930’s Miss Hannigan wanted to delete orphan Annie, but now we have child neglect laws that prohibit that. Good thing we now have SQL.

Lets say we have two SQL tables that contains names of team members. One is for softball and one is for baseball. We want to remove all players from softball that do not play baseball. You could do this in a loop or a subquery, but if you have team data from the past 100 years this may become quite costly.

Here’s a quick way to make this happen:

DELETE S
FROM Softball S
LEFT JOIN Baseball B ON (S.Name = B.Name)
WHERE S.Name IS NULL

What this is doing is matching the table Softball with Baseball. Any orphaned Softball players (no match with Baseball) end up getting deleted.

Advertisements