How to get the result set's intersection?

Question

Result set1:

select code from quote where cond1

Result set2:

select code from vix  where cond2

I want a set containing code which is the intersection of Result set1 and Result set2.

select code from quote where cond1 and code in (select code from vix  where cond2);

It can't work!Same error info for both GMB and Akina 's answer.

RROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Let's display the table structure:

show create table quote;
+-------+------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------+-----------------------------------------------------------------
| quote | CREATE TABLE `quote` (
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `code` text COLLATE utf8mb4_unicode_ci,
  `date` date DEFAULT NULL,
  `open` double DEFAULT NULL,
  `high` double DEFAULT NULL,
  `low` double DEFAULT NULL,
  `close` double DEFAULT NULL,
  `volume` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17173979 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+--------------------------------------------------------------------+

show create table vix;
+-------+-------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                  |
+-------+--------------------------------------------------------------------+
| vix   | CREATE TABLE `vix` (
  `code` text,
  `span` smallint(6) DEFAULT NULL,
  `vix` double DEFAULT NULL,
  `extrem_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

Search code set from table quote and vix.

select code from quote where volume > 10000000 and date='2020-08-24';
+-------+
| code  |
+-------+
| zom   |
| ibio  |

Note : about 120 rows.

select code from vix  where span >30;
+---------+
| code    |
+---------+
| canf    |
| ensv    |

Note : about 1100 rows. Get the intersection of the two result set:

MariaDB [stock]> select code 
    -> from quote q 
    -> where 
    ->     volume > 10000000 and date='2020-08-24'
    ->     and exists (select 1 from vix v where span >30 and (v.code = q.code));
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

Answer 1

Your (pseudo) query query should do what you want. I find, however, that using exists is more efficient when the list of values gets bigger. I would phrase this as:

select code 
from quote q 
where 
    cond1
    and exists (select 1 from vix v where cond2 and v.code = q.code)