Jeff Moden has written some good articles here at SSC about triangular joins, set based method, and RBAR. I think those articles would be better able to fully explain it.

In short, set-based queries perform an action against a group of records (1 or more records) at the same time. Cursors and loops (RBAR) perform an action to one record at a time. This kind of processing can be intensive on resources and cause other issues.

My sig has one such article by Jeff. Check it out.