I have a user relation to address (1 to 1).

So the address has two types which is a type column.

One is the VerifiedAddress and the other one is the SearchAddress.

So the user who had verified his address has two entries in the addresses table.

In case you wonder why is that, the reason is that user can go to a travel and change the SearchAddress but the VerifiedAddress remains the same.

I'm trying to find all the users who haven't got verified their address. I have some user list from which I know who has verified address and who hasn't got it.

This is what I have tried the last, not exactly what I wanted :

select id from addresses
where user_id in (8112, 18620, 23, 19668, 10212)
and type = 'VerifiedAddress';

Gives me all the verified users, but if I change the type != VerifiedAddress I still get the same results because users who have verified their address have two entries in a addresses table, this time I get the user_ids from the SearchAddress.

How can I get this data from addresses table?

Note: SearchAddress can be null as well as VerifiedAddress. Naturally people who don't have searchaddress don't have the verified as well.

1 Answer 1


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:

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:

FROM users
  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.

  • thanks Craig, this was what I was looking for, does the using of aliases like addresses a, have any impact on the performance? Commented Feb 20, 2014 at 18:13
  • @GandalfStormCrow: No measurable influence. Commented Feb 21, 2014 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.