Thank you all for your answers. I'll try them on the real data tomorrow and see which performs the quickest.
A couple of other questions ... If I stick with #tblNoEmail as just having one column - EmailAddress - what sort of index should I put on it?
And I will need, wherever a list of email addresses is displayed in the front end, to highlight any email addresses that are in #tblNoEmail
I could do this ...(or similar using the CTE solution above)
SELECT
e.EmailAddress, CASE WHEN ne.EmailAddress IS NOT NULL THEN 'No' ELSE 'Yes' END AS [CanUseEmailAddress] FROM #tblContacts c
JOIN #tblEmails e ON c.ContactID = e.ContactID
LEFT JOIN #tblNoEmail ne ON e.EmailAddress = ne.EmailAddress;
Is there a better way to do that?
That seems to be a good way to do based on your required output.
The simplest solution is often the best one especially in SQL, and also the query you have written is in an uncomplicated way, and therefore you do not have to worry about he best way the optimizer would implement the solution(SQL is a declarative language, i would rather not to specify the algorithm the database should choose to fetch me the data; rather i would keep things such as statistics up to date, and add indexes if it makes sense, add constraints etc anything that helps the optimizer to make smart decisions)
Some people worry about the join "order" in a query; the database would be smart enough to see the query you are requesting as a whole and decide which joins it should do first then the next etc.
True enough about trusting the optimizer, but remember that it's only as good as the statistics you keep....