LEFT JOIN based on content of a field in matched rows
By moving c.contacttype='email' from WHERE to ON, the LEFT JOIN properties that if it doesn't match a criteria a NULL row appears behaviour occurs:

select u.firstname,u.lastname,u.title,c.contactdetails
from users u
left join contact c
  on u.id=c.userid and c.contacttype='email'