MySQL - Left join with condition for matched row-CodePudding

I have two tables User and Note

User
 id   name
 1    test1
 2    test2
 3    test3

Note
 id   user_id   memo
 1       1      ERROR
 2       2      SOMETHING

Then, I want to get all users from User table which are not existed in Note table and if it was existed in Note table, it should not have 'ERROR' and 'TEST' in memo column.

Expected data return: test3, test2

I tried this:

SELECT User.id, User.user_name FROM User
LEFT JOIN Note ON User.id = Note.user_id
WHERE Note.memo NOT IN ('ERROR', 'TEST');

But It returned: test1, test2

Then I tried this:

SELECT User.id, User.user_name FROM User
LEFT JOIN Note ON User.id = Note.user_id 
AND Note.memo NOT IN ('ERROR', 'TEST');

But it returned: test1, test2, test3

So how can I get my expected result?

CodePudding user response：

SELECT *
FROM User
WHERE NOT EXISTS (
    SELECT NULL
    FROM Note 
    WHERE User.id = user_id
      AND memo IN ('ERROR', 'TEST')
);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4f0e379cd3f1d88c45502a7c87e7bdc7