How should I use NOT IN statement in MySQL

Question

I am trying to use NOT IN statement with MySQL. However, I get 0 row with code below (no syntax error). I am sure there should be more than 0 row with the statement. What syntax should I adjust?

SELECT DISTINCT member_id
FROM client_payments
INNER JOIN client_purchase_records ON client_purchase_records.id = client_payments.purchase_record_id
WHERE status = 1
AND client_payments.created_at > '2021-10-28 00:00:00'
AND client_payments.created_at < '2021-10-31 23:59:00'
NOT IN(
    SELECT DISTINCT member_id
    FROM client_payments
    INNER JOIN client_purchase_records ON client_purchase_records.id = client_payments.purchase_record_id
    WHERE status = 1
    AND client_payments.created_at > '2020-9-30 00:00:00'
    AND client_payments.created_at < '2021-10-27 23:59:00'
);

Difference about two query is mainly about created_at column, I want to do "set difference operation" with period A(2021-10-28 00:00:00 - 2021-10-31 23:59:00 )and period B(2020-9-30 00:00:00 - 2021-10-27 23:59:00)

1. I want to query out member_id who pay during 2020-9-30 00:00:00 - 2021-10-27 23:59:00

2. Subtract with member_id who pay during 2021-10-28 00:00:00 - 2021-10-31 23:59:00

3. Finally I get member_id who pay during 2021-10-28 00:00:00 - 2021-10-31 but not pay during 2020-9-30 00:00:00 - 2021-10-27 23:59:00 ( new member_id never show before)

Answer 1

You presumably want to be saying AND member_id NOT IN (...your subquery...); as is, it is using the previous condition instead of member_id.

Also, it looks like you want >= and <=, not > and <.

I would do this like so instead:

SELECT member_id
FROM client_payments
INNER JOIN client_purchase_records ON client_purchase_records.id = client_payments.purchase_record_id
WHERE status = 1
AND client_payments.created_at >= '2021-09-30 00:00:00'
AND client_payments.created_at <= '2021-10-31 23:59:00'
GROUP BY member_id
HAVING MIN(client_payments.created_at) >= '2021-10-28 00:00:00'

Answer 2

no syntax error

The error is in the logic.

Your condition, after adding the parenthesis according to operators priority, looks like

AND ( (client_payments.created_at < '2021-10-31 23:59:00') NOT IN ( {subquery} ) )

I.e. the result of comparing client_payments.created_at < '2021-10-31 23:59:00' (which is 0, 1 or NULL) is searching in the subquery output which is obviously illogical.