0

I have a table abc that has 2 columns:

id INT(3) name VARCHAR(10)

The default collation for this table and for all of its column is utf8_unicode_ci

I then have another table xyz that has 2 columns:

id INT(3) name VARCHAR(10)

The default collation for this table and for all of its column is also utf8_unicode_ci

Now I am firing this select statement:

SELECT NAME FROM abc WHERE ID NOT IN (SELECT ID FROM XYZ);

I have checked the collations of both the tables and they are same, still, it gives me the error : illegal mix of collations (utf8_general_ci implicit) and (utf8_unicode_ci implicit) for operation =

I also tried an alternate for NOT IN i.e. by using LEFT JOIN but it still gives me the same error.

I have been stuck on this for a long time now. Any help is appreciated.Thanks

Allan Fernandes
  • 17
  • 1
  • 9
  • "I have checked the collations of both the tables and they are same, still, it gives me the error : illegal mix of collations (utf8_general_ci implicit) and (utf8_unicode_ci implicit) for operation =" learn one thing well programming computers do not lie.. – Raymond Nijland Mar 20 '18 at 15:40
  • 1
    show `SHOW CREATE TABLE abc` and `SHOW CREATE TABLE xyz` outputs. – Raymond Nijland Mar 20 '18 at 15:41
  • The full query and error would help too since I can't see how you would get a collation error comparing two INTEGER ID values. – Paul Campbell Mar 20 '18 at 15:48

1 Answers1

0

A likely workaround:

SELECT NAME
    FROM abc
    LEFT JOIN XYZ  USING(ID)
    WHERE XYZ.ID IS NULL;

Another help may be

SET NAMES utf8 COLLATE utf8_unicode_ci;
Rick James
  • 135,179
  • 13
  • 127
  • 222