You can use a CROSS APPLY as well

SELECT M.member_name,

M.member_plan,

LS.ncqa_code

FROM #member M

CROSS APPLY (

SELECT TOP 1 L.ncqa_code

FROM #language_source AS L

WHERE L.member_plan = M.member_plan OR L.member_plan IS NULL

ORDER BY L.member_plan DESC

) AS LS