Here's an approach using the OUTER APPLY operator available since SQL 2008 (so, it's nothing new in 2012 ...).

Please note that I changed UNION to UNION ALL for the query using #league since I wouldn't expect any duplicates. But if there are dups, please let us know how you'd differentiate where those are coming from (either the #league table or the #teamplayer table).

SELECT DISTINCT

CASE WHEN TP.TeamPlayerID IS NULL THEN NULL ELSE TP.PlayerID END as PlayerID,

CASE WHEN TP.TeamPlayerID IS NULL THEN 'FreeAgent' ELSE x.PlayerName END as PlayerName

From #teamplayer TP

OUTER APPLY

(

SELECT P.PlayerName

FROM #team T

JOIN #player P on TP.PlayerID = P.PlayerID AND TP.TeamID = T.TeamID

)x

UNION ALL

SELECT L.LeagueID as PlayerID, L.LeagueName as PlayerName

FROM #league l