Using "not in" on WHERE with AND?

Question

I'm trying to get a better understanding of how "not in" works with WHERE in MySQL.

For example:

SELECT * FROM current_mailing_list 
WHERE address1 NOT IN
    (select address1 from old_mailing_list) AND
    city not in (select city from old_mailing_list);

In the above example, the purpose of the query is to list mailing addresses which are new. address1 is a street address such as 1234 N. Main St. The problem happens when 1234 N. Main St occurs in more than one city, which can happen. So I decided to add city to it to make it more unique.

My question is, is this doing what I expect it to do? Meaning, it should find those street addresses (address1) that don't exist in the old_mailing_list AND then make sure they have a different city.

I have done this, just with the address1:

SELECT * FROM current_mailing_list 
WHERE address1 NOT IN 
    (select address1 from old_mailing_list);

and it produced a much larger list (about 10 times the size). So I wanted to add city to this. Or is my logic in the entirely wrong and need another approach?

Answer 1

Your current query will not return new addresses where EITHER their "address1" or "city" appears at all in the old mailing list. I think you want to select cities where they don't both appear together, like so:

SELECT *
FROM current_mailing_list c
WHERE NOT EXISTS
(
    SELECT 1
    FROM old_mailing_list
    WHERE
        address1 = c.address1
        AND city = c.city
)

Quite literally; select everything from the current mailing list where there is no record in the old mailing list with the same city and address1.