How to fix 'Illegal mix of collations' issue

You might have come across the following error in MySQL:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

after executing a SQL SELECT statement as simple as:

SELECT *
FROM MyPersons
WHERE name NOT IN (SELECT name FROM MyUsers);

You can view an on-line demo of this case here:

We can understand the origins of this error if we check the MySQL on-line manual:

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

So, what causes the problem in our case is that despite the two tables, MyPersons, MyUsers, share the same character set, their collations are not the same. Due to this fact MySQL is unable to perform a comparison between the fields called name.

Let us have a look at how the two tables are created:

CREATE TABLE MyPersons (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci
);

CREATE TABLE MyUsers ( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci
);

To solve the problem we can override the collation of table MyUsers using the COLLATE clause:

SELECT *
FROM MyPersons 
WHERE name NOT IN (SELECT name COLLATE utf8_general_ci
                   FROM MyUsers);