NOT IN The statement does not work for non-primary key values ​​in mysql

Question

I read a lot of Q & A regarding NOT IN ie operator. If I use the IN operator to filter NULL values ​​and spaces, it does not work. Why? and many others. But the problem is that when we use a non-operator for a non-primary key, it does not return a response.

SELECT * FROM temp_customers WHERE temp_customers.fk_my_id NOT IN (SELECT fk_my_id FROM customers) 

Where fk_my_id is a non-primary key and may be a string.

Any idea please?

Answer 1

use group_concat here to separate fk_my_id with a comma

 SELECT * FROM temp_customers WHERE temp_customers.fk_my_id NOT IN (SELECT GROUP_CONCAT(fk_my_id) FROM customers) # when fk_my_id is INTEGER output (1,2) SELECT * FROM temp_customers WHERE temp_customers.fk_my_id NOT IN (SELECT GROUP_CONCAT("'",fk_my_id,"'") FROM customers) # when fk_my_id is VARCHAR output ('1','2') 

Answer 2

I suppose you used the wrong column in the query: (SELECT fk_my_id FROM customers) . It must be pkId of your customer table.

 SELECT * FROM temp_customers WHERE temp_customers.fk_my_id NOT IN (SELECT pk_my_id FROM customers)