NOT IN returns wrong results with where

Question

i am new in MYSQL and i am asking for help which NOT IN function is not working

select * 
from `oc_order` 
where `customer_id` NOT IN (
    SELECT `customer_id` 
    FROM `oc_order` 
    WHERE `order_status_id` LIKE "3" 
      AND `last_update` > '2022-12-28' 
      AND `customer_id` > 0 
      AND `customer_group_id` > 0
    );

I need to find out customers who are not active during this year. Could you please give me some solution for solve this problem? thanks

i checked inside NOT IN is working fine:

SELECT `customer_id` 
FROM `oc_order` 
WHERE `order_status_id` LIKE "3" 
  AND `last_update` > '2022-12-28' 
  AND `customer_id` > 0 
  AND `customer_group_id` > 0 

Answer 1

I need to find out customers who are not active during this year.

Your query doesn't match this description. First of all, you should be selecting from a customer table, because inactive customers may not even have an entry in the order table that you are using in your query.

Then you define the year 2023 to start after midnight of December 28, 2012, which is somewhat special :-) And you declare a customer "active" when they have an order that was updated in that time (so every update means activity on the part of the customer?) and the order status matching some string expression. Also, the customer ID must be greater than zero and the customer group ID, too.

Can an order really be assigned to a negative customer ID? Or is this to avoid unassigned orders (customer ID NULL)? Are such orders even possible? And what does the customer group ID in the order table represent? Why is the customer not assigned a group in the customer table? Can a customer belong to a different group with each order, or what else is it that the group ID is supposed to mean here? order_status_id LIKE "3" is a weird expression by the way. First of all you should use standard string delimiters, which is the single quote, not the double quote. Then you are using LIKE without any pattern (% or _), so why not use = right away? And is the status ID really a string, anyway? A date literal should start with the word DATE by the way to be compliant with standard SQL.

The query that I would expect would look something like this:

select *
from customer c
where not exists
(
  select null
  from oc_order o
  where o.customer_id = c.customer_id
  and o.order_status_id = 3
  and o.last_update > date '2022-12-28'
)
order by c.customer_id;

While this may not completely answer all your questions, it should be sufficient to solve your struggles with the query.