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.