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)

CodePudding user response：

You want and not exists. Not in does something very different and is becoming part of the preceding condition.

See https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html

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

CodePudding user response：

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.