Using "not in" on WHERE with AND?

Question

I am trying to better understand how "does not work" with WHERE in MySQL.

For instance:

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 goal of the request is to list the new mailing addresses. address1 is the street address, for example 1234 N. Main St The problem arises 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 what I expect from him? So, he must find these street addresses (address1), which are not in old_mailing_list AND , then make sure that they have another city .

I did this simply with address1:

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

and he released a much larger list (about 10 times more). So I wanted to add city to this. Or is my logic completely wrong and needs a different approach?

Answer 1

To use NOT IN , you will want to combine the city and address in the same sentence as MySQL supports this (other RDBMS do not support this method). As others pointed out, you will not get the desired results using both separately.

Try it if you want to use NOT IN :

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

SQL Script Demo Example

I prefer to use LEFT JOIN for this personally, but also check for NULL:

 SELECT c.* FROM current_mailing_list c LEFT JOIN old_mailing_list o ON c.address1 = o.address1 AND c.city = o.city WHERE o.address1 IS NULL 

Answer 2

In your current request, new addresses will not be returned, where EITHER their "address1" or "city" appears generally in the old mailing list. I think you want to select cities in which they do not appear together, for example:

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

Pretty literally; select everything from the current mailing list that does not have an entry in the old mailing list with the same city and address1.

Answer 3

If 1234 N. Main St. is the address for both the city on the old mailing list and the city on the new mailing list, your request will exclude BOTH addresses. Your request does exactly what it says it does: you will receive addresses where both the street address and the city are not on the old mailing list. If they appear in an address on the old mailing list, that address will not be received.