Selecting distinct records
This is called a left anti-join. You want all users who don't have a verified address. So you do a left join to find all users and whether they do have a verified address, then discard all the ones that have a verified address.

Something like:

SELECT *
FROM users
LEFT OUTER JOIN addresses a
ON (users.id = a.user_id AND a.type = 'VerifiedAddress')
WHERE a.user_id IS NULL;


This may be easier to understand when expressed with NOT EXISTS as:

SELECT *
FROM users
WHERE NOT EXISTS (
  SELECT 1 
  FROM addresses a
  WHERE users.id = a.user_id
  AND a.type = 'VerifiedAddress'
)


both of which will optimize to the same query plan, in general.