MySQL error: NOT IN + subquery using GROUP BY HAVING returns nothing

Question

I am querying this table:

SKU aaaa bbbb bbbb NULL 

Here's the request:

 select * from TEST as N where N.SKU NOT IN (select SKU from TEST group by SKU having count(*)>1); 

I expect the request to return 'aaaa', but it does not return anything.

The reason I expect it is because the subquery below returns only "bbbb":

 select SKU from TEST group by SKU having count(*)>1 

Therefore, 'aaaa' NOT IN in the subquery.

To show the error, copy and paste these instructions into your IDE ID to create the schema:

 drop table if exists TEST; create table TEST( SKU varchar(255) ); insert into TEST values('aaaa'),('bbbb'),('bbbb'),(NULL); 

Answer 1

This works fine if the subquery returns only one row:

 SELECT * FROM TEST as N WHERE N.SKU NOT IN ( CAST((select SKU from TEST group by SKU having count(*)>1) AS CHAR) ) 

However, if it returns more than 1 row, an error will be triggered. Another solution (if the subquery returns more than 1 row):

 SELECT * FROM TEST as N WHERE N.SKU IN ( (select SKU from TEST group by SKU having count(*) <= 1) )